Getting 'Type mismatch' Run-time error when comparing values in different/separate table columns

Question

Basically I just want to check if two cells in a separate table columns contain the same string, and if they do, I want to assign another string just an .Offset(, 1) away from the original cell.

I'm trying to solve this for several hours now. I've also tried comparing .text properties and converting with CStr() but still getting mismatch error.

Here's a sample

Dim c1 As Range
Dim c2 As Range

For Each c1 In tranTab.DataBodyRange.Columns(1)
    For Each c2 In rawTab.DataBodyRange.Columns(9)
        If c2.Value = c1.Value Then
            c2.Value = c1.Offset(, 1).Value
        End If
    Next c2
Next c1

Debug highlights this line:

If c2.Value = c1.Value Then

and throws:

'Type mismatch' Run-time error

In Locals window both are shown as Variant/Variant type. It's my first post/question and I'm almost sure I've done my research to find every similar piece of info about mismatch issues but none exhausting the topic when it comes to operating on tables/ListObjects.

Answer 1

The loop is not through cells, but it is through the whole column. Try this and check that the address is the one of the column:

Sub TestMe()

    Dim c1 As Range
    For Each c1 In Worksheets(1).ListObjects("tranTab").DataBodyRange.Columns(1)
        Debug.Print c1.Address
    Next c1

End Sub

In order to loop through the cells, add .Cells at the end of the for-each loop:

For Each c1 In Worksheets(1).ListObjects("tranTab").DataBodyRange.Columns(1).Cells