question

AnnaRankin-4987 avatar image
0 Votes"
AnnaRankin-4987 asked WendyLi-MSFT edited

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

office-vba-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnnaRankin-4987 avatar image
0 Votes"
AnnaRankin-4987 answered Viorel-1 commented

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.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

The new error is "Object required" and it highlights the first line of the If statement.136776-vba-error-object-required2.png


0 Votes 0 ·
Viorel-1 avatar image Viorel-1 AnnaRankin-4987 ·

Maybe thisworksheet is not defined or you should use the name of the worksheet (e.g. Sheet1) instead of thisworksheet?


0 Votes 0 ·