Runtime Error 424 - not sure why.

[JeffGrant]

Hi All,

I have this code to loop through a column in a table to check if the string value for time is earlier than the current string value of now. Hever, I am getting a 424 error and I don't know why.

I am getting the Run-Time error 424: Object required from the For Each cell in column.DataBodyRange line.

This is my code:

Sub SkipEarlierTimes()

    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim column As ListColumn
    Dim cell As Variant
    Dim currentTime As Date

    ' Set the worksheet
    Set ws = Sheet10
   
    ' Set the table
    Set tbl = ws.ListObjects("API_Races_Next_Starts")
   
    ' Set the specific column
    Set column = tbl.ListColumns(4)
   
    ' Get the current time
    currentTime = Now
   
        ' Loop through each cell in the column
           For Each cell In column.DataBodyRange
            ' Check if the time string is earlier than the current time
            If TimeValue(CDate(cell.Value)) < TimeValue(CDate(currentTime)) Then
                ' If earlier, move to the next cell
                'MsgBox "early, move on " & Format(cell.Value, "hh:mm")
                Set cell = cell.Offset(1, 0)
            Else
                'MsgBox "Cell time is after now " & Format(cell.Value, "hh:mm")
                Exit Sub
            End If
        Next cell
End Sub

can somebody please point me in the right direction. I suspect I am not declaring the cell variable correctly, but am not sure.

Thanks for your help.

 

[JeffGrant]

UPDATE:

I think I worked it out. At the start of the day, the table being checked does not have any values in column 4.

I have added the line:

On Error Resume Next and everything seems ok now.

Am I on the right track?

Thanks

 

[RoryA]

You should never just add On Error Resume Next without knowing why. And you should always turn it off as soon as possible afterwards.

If your table is empty, then the databodyrange property can fail. I'd test for that like this:

if tbl.listrows.count > 0 then
        ' Loop through each cell in the column
           For Each cell In column.DataBodyRange
            ' Check if the time string is earlier than the current time
            If TimeValue(CDate(cell.Value)) < TimeValue(CDate(currentTime)) Then
                ' If earlier, move to the next cell
                'MsgBox "early, move on " & Format(cell.Value, "hh:mm")
                Set cell = cell.Offset(1, 0)
            Else
                'MsgBox "Cell time is after now " & Format(cell.Value, "hh:mm")
                Exit Sub
            End If
        Next cell
   end if

 

[JeffGrant]

as always Rory. Thanks for your advice. I'll give it a go.