在VBA中定义InStr

debugcn 发表于 Dev

安东尼

在他们更改了我的日期/时间格式之前，有一行代码一直有效。我需要从中提取日期，小时和分钟：

StringOfText：StringOfText1开始运行，结果：XXXXXXXX 2020-01-21_11h49m38s

这是我正在使用的，但是在运行excel宏时出现错误不匹配：

Start_Time = _
  DateValue(Left(Right(Left(Cells(i, x).Value, (InStr(Cells(i, x).Value, "m*s") + 2)), 20), 10)) + _
  TimeValue(Left(Right(Left(Cells(i, x).Value, (InStr(Cells(i, x).Value, "m*s") + 2)), 9), 2) & _
  ":" & Left(Right(Left(Cells(i, x).Value, (InStr(Cells(i, x).Value, "m*s") + 2)), 6), 2))

我知道我在那里需要一个“赞”，但是当我添加它时，我会得到更多的错误。代码在“ m * s”具有“ min”（之前使用的行的末尾）之前有效。i和x在此代码段上方已预定义。

任何想法或帮助将不胜感激。

尊尼

      dim s as string
    
'this is the cell value in your loop
      s = Cells(i, x).Value
        
      Dim i As Long
    
'locate as a reference point the underscore as a known, reliable anchor
      i = InStr(1, s, "_", vbTextCompare)

'Once we know where the data is we can remove the formatting from the string
' that prevents us from using VBA CDate - "_", "h", "m", "s"

' use mid string with no third parameter to get all of the remaining string from the start point
' use len("yyyy-mm-dd") rather than just 10 as a magic number for clarity
      s= Mid(s, i - len("yyyy-mm-dd"))

' make necessary replacements to change "2020-01-01_11h20m34s" to
' 2020-01-01 11:20:34" which can be parsed by CDate
      s = Replace(s, "_", " ")
      s = Replace(s, "h", ":")
      s = Replace(s, "m", ":")
      s = Replace(s, "s", "")
      start_time=CDate(s)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。