Excel refer table cells by column name instead of offset-CodePudding

Please consider the below code, it's iterating over "Country" column of the table and changing the cell 2 positions to it's right to "Changed". I need to stop using the offset and refer to the two columns to right by it's header name "Capital". How to do it?

Any help greatly appreciated!

For each lookupcell in lookupsheet.Range("A1:A10")

For Each updatecell in updatesheet.Listobjects(1).Listcolumns("Country").databodyRange

if updatecell.offset(0,2).value= lookupcell.Value 'Need to change this line, remove offset and use column name instead

if updatecell.offset(0,3).value= lookupcell.offset(0,2).Value 'Need to change this line, remove offset from Cell1 and use column name instead

Then.....

Next updatecell

Next lookupcell`

CodePudding user response：

With ActiveSheet.ListObjects(1)
    For i = 1 To .DataBodyRange.Rows.Count
        If [Your Test] Then
            .ListColumns("Capital").DataBodyRange(i).Value = "Changed"
        End If
    Next i
End With

Change [Your Test] as required, for example:

With ActiveSheet.ListObjects(1)
    For i = 1 To .DataBodyRange.Rows.Count
        If .ListColumns("Country").DataBodyRange(i).Value = "A" Then
            .ListColumns("Capital").DataBodyRange(i).Value = "Changed"
        End If
    Next i
End With