CODEWITHSUNDEEP
excelvba
Anthony
Anthony

Reputation: 3

Defining a InStr in VBA

Have a line of code that was working until they changed the date/time format on me. I need to pull the date, hour and min from this:

StringOfText: StringOfText1 beginning run, result: XXXXXXXX 2020-01-21_11h49m38s

This is what I'm using, but getting an error mismatch when running the excel macro:

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))

I know I need a "Like" in there, but when I add it I get even more errors. Code was working before when "m*s" had "min" which was at the end of the previously used line. i and x are predefined above this snippet of code.

Any thoughts or help would be appreciated.

Upvotes: 0

Views: 94

Answers (2)

JohnnieL
JohnnieL

Reputation: 1231

      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)

Upvotes: 0

Scott Craner
Scott Craner

Reputation: 152525

If your date time is always last in the string then using split and replace will return the date/time:

Sub kljl()
    Dim str As String
    str = "StringOfText1 beginning run, result: XXXXXXXX 2020-01-21_11h49m38s"
    
    Dim strArr() As String
    strArr = Split(str, " ")
   
    Dim dte As Date
    dte = CDate(Replace(Replace(Replace(Replace(strArr(UBound(strArr)), "_", " "), "h", ":"), "m", ":"), "s", ""))
    
    Debug.Print dte
End Sub

IF it is not always on the end then we can loop the strArray and find the first date/time

Sub kljl()
    Dim str As String
    str = "StringOfText1 beginning run, result: XXXXXXXX 2020-01-21_11h49m38s"
    
    Dim strArr() As String
    strArr = Split(str, " ")
    
    Dim i As Long
    i = 0
    
    Dim dte As Date
    dte = 0
   
    Do
        On Error Resume Next
            dte = CDate(Replace(Replace(Replace(Replace(strArr(i), "_", " "), "h", ":"), "m", ":"), "s", ""))
        On Error GoTo 0
        i = i + 1
    Loop Until dte > 0
    
    Debug.Print dte
End Sub

Upvotes: 1

Related Questions