VBA: Keep getting compile error: Sub or Function not defined when trying to run my routine

Anna Rankin 1 Reputation point
2021-09-30T20:12:25.803+00:00

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

0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 112.1K Reputation points
    2021-09-30T20:43:23.75+00:00

    Continue by removing the third 'r' from timeArrary and replace Next cell with Next i.

    0 comments No comments

  2. 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.