Testing the date in a table - can't do it row by row?

[TheRedCardinal]

I have a table that has several columns in it. I have a control sheet that has several tests on the data each time the control sheet is activated. These are typically syntax / format issues.

I originally wrote this code:

Set TestCol = Tbl.HeaderRowRange.Find("Date")
TestResult = True

    For Each CellA In Tbl.DataBodyRange.Columns(TestCol.Column)
    
        If Month(CellA.Value) <> WS1.Range("RepMonth") Then
    
            TestResult = False
    
        End If

    Next CellA

    TestNow = WS1.Range("TableDates")

    If TestResult = False Then
    
        WS1.Range("TableDates") = "No"
        
    Else
        
        WS1.Range("TableDates") = "Yes"
    
    End If
    
    If WS1.Range("TableDates") <> TestNow Then WS1.Range("TableDates").Offset(0, 1) = Date

This gave me a type mismatch error and I discovered using the Immediate window that CellA had an address of A4:A230 - rather than just a specific cell.

So I thought I could define a range first, and then run a For/Each on that range. But the same result.

Set TestCol = Tbl.HeaderRowRange.Find("Date")
    TestResult = True
    Set TestRange = Tbl.DataBodyRange.Columns(TestCol.Column)

    For Each CellA In TestRange
    
        If Month(CellA.Value) <> WS1.Range("RepMonth") Then
    
            TestResult = False
    
        End If

    Next CellA

    TestNow = WS1.Range("TableDates")

    If TestResult = False Then
    
        WS1.Range("TableDates") = "No"
        
    Else
        
        WS1.Range("TableDates") = "Yes"
    
    End If
    
    If WS1.Range("TableDates") <> TestNow Then WS1.Range("TableDates").Offset(0, 1) = Date

Have I misunderstood what the For Each/Next command does?

Note all references are correct and valid, and the ranges are exactly as expected.

 

[StephenCrump]

Try: For Each CellA In Tbl.DataBodyRange.Columns(TestCol.Column).Cells

Compare, for example:

Sub DoesntWork()

    Dim r As Range
    
    For Each r In Range("A1:C3").Columns(1)
        Debug.Print r.Address
    Next r

End Sub
Sub Works()

    Dim r As Range
    
    For Each r In Range("A1:C3").Columns(1).Cells
        Debug.Print r.Address
    Next r

End Sub

 

[Anthony47]

In addition to what already suggested by Stephen, above, you might use For Each CellA In Tbl.ListColumns(TestCol.Column).DataBodyRange /Next CellA
Please also note that by using Set TestCol = Tbl.HeaderRowRange.Find("Date") and later TestCol.Column you will calculate the Worksheet column, that might be different from the Table column, if the Table does not start from column A

A possible alternate method:

Dim DtCol As Long
DtCol = Application.Match("Date", Tbl.HeaderRowRange, False)    'The "Date" columns


For Each CellA In Tbl.ListColumns(DtCol).DataBodyRange          '
' etc
'etc