Continue by removing the third 'r' from timeArrary and replace Next cell with Next i.
VBA: Keep getting compile error: Sub or Function not defined when trying to run my routine
I'm new to VBA and is trying to get the following VBA code to run in excel but keep getting an "Sub or Function not defined" error. I'm hoping someone can help for I'm on a time crunch. I would like the code to read time string values from source cells and extract out the hour and min and assign them to separate cell. Below is my code. Feedback is appreciated.
Sub timeFormat2()
Dim timeArray() As Variant
Dim hour As String
Dim min As String
Dim tempMin As String
Dim i As Long
rTimeCol = "d"
For i = 2 To 5
'Only hour exist
If ((InStr(thisworksheet.Range(i & "rTimeCol").Value, "h") > 0) And (InStr(thisworksheet.Range(i & "rTimeCol").Value, "m") = 0)) Then
timeArray = Split(thisworksheet.Range(i & "rTimeCol").Value, "h")
hour = timeArray(1)
min = "0"
Range("v" & i).Value = Trim(hour)
Range("w" & i).Value = min
Exit For
'Only minutes exist
ElseIf ((InStr(thisworksheet.Range(i & "rTimeCol").Value, "h") = 0) And (InStr(thisworksheet.Range(i & "rTimeCol").Value, "m") > 0)) Then
'timeArray = Split(Range("rTimeCol" & i).Value, "m")
tempMin = thisworksheet.Range(i & "rTimeCol").Value
hour = "0"
min = Right(tempMin, Len(tempMin) - 1)
Range("v" & i).Value = hour
Range("w" & i).Value = Trim(min)
Else 'hour and minutes
'If InStr(Range("e2").Value, "h") > 0 Then
timeArray = Split(thisworksheet.Range(i & "rTimeCol").Value, "h")
hour = timeArray(1)
timeArray = Split(thisworksheet.Range(i & "rTimeCol").Value, " ")
min = Right(timeArrary(2), Len(timeArray(2)) - 1)
Range("v" & i).Value = Trim(hour)
Range("w" & i).Value = Trim(min)
'Exit For is not necessary with the Else clause
End If
Next cell
End Sub
2 answers
Sort by: Most helpful
-
-
Anna Rankin 1 Reputation point
2021-10-01T02:27:28.633+00:00 Thank you that fixed the compile error but now a new "object error" is happening. Since I'm new to vba, I'm not sure if code for the if statement is an appropriate approach to read values from cells in a spreadsheet. Further assistance from you is appreciated.