Cell references of range and listobject differ, looking for explanation and solution.

[hombibi]

I am trying to loop through the cells of several copied listobjects (table) in a workbook

The problem I run into is that the cell references of the "(each) cell loop" are different from the listobject.range.cell/row references, and equal to the offset/references of the first cell of the listobject. (so no problem when the listobjects start at A1)

To investigate I created two listobjects mylo1 and mylo2 of 4x4 cells, starting on B3 and the second on G4 somewhere in a workbook. I then populated the cells of each listobject with the same data, (AA, BB, CC, DD, FF, GG, HH, II, JJ, etc), and had the headers created by excel when converting the range to a table. For ease of use I name listobjects.

In vba I investigated the issue with message boxes:

      For Each cell_1 In mylo_1.Range
         MsgBox cell_1.Address, vbOKOnly, "Plain"
         MsgBox mylo_1.Range(cell_1.Row, cell_1.Column).Address, vbOKOnly, "Lo address"
      Next

The result should be that both msgboxes display the same reference values for the same cell, or at least that is what I expect and need, however immediately the first run gives me

$B$3, $C$5

It looks as if the reference of the "for each cell" is absolute, while the cell reference of the listobject is relative, or offset with the reference of the first cell of the loop.

The question is how I can loop through the first listobject (for each) and use the reference from the current cell to reference the corresponding cell in the second listobject.

P.S. the listobjects could be anywhere in the workbook, I reference by listobject name to avoid having to physically locate the listobjects.

Anyone who understands what's going on or how I could alleviate the issue?
Thanks.

 

[John_w]

Welcome to MrExcel forums.

mylo_1.Range(1, 1) refers to the 1st row and 1st column in the ListObject, i.e. its top left cell (B3)

Therefore, for the first iteration of the loop, which starts at row 3, column 2, mylo_1.Range(3, 2) refers to the 3rd row and 2nd column in the ListObject, i.e. cell C5.

To make the For Each loop reference the correct cells in mylo_1.Range, simply adjust the row index by the start row of the ListObject and the column index by the start column of the ListObject.

    With mylo_1
        Debug.Print .Range.Row, .Range.Column  'start row & column of ListObject
        For Each cell_1 In .Range
            Debug.Print "cell_1 " & cell_1.Address, "mylo_1 " & .Range(cell_1.Row - .Range.Row + 1, cell_1.Column - .Range.Column + 1).Address
        Next
    End With

Cells Within Ranges explains more about referring to cells within a range, but you would still need the simple arithmetic above if you use the Item method or its shorthand version.

 

[hombibi]

Thanks John_w, that solves the issue, and the (lack of) understanding.


It all works now, exactly as you described. Lots of repeated cell reference code but that is secondary.


Thanks very much for your help.
Hombibi

 

[John_w]

Here is the single indexing method described on that page, for which you need a counter variable in the loop. This seems neater because it doesn't matter where the ListObject starts.

    Dim i As Long
    With mylo_1
        i = 0
        For Each cell_1 In .Range
            i = i + 1
            Debug.Print "cell_1 " & cell_1.Address, "mylo_1 " & .Range(i).Address
        Next
    End With

 

[hombibi]

Thanks John_w,

I need to look into that: as I compare two tables cell by cell (synchronised?), I use the reference of the first cell to locate the corresponding cell in the second, regardless of where each of the tables is positioned. For now I have solved the reference clutter by creating two additional range objects that I set in every loop.
Trying your example I think I would still need to include some offset to relate the cell in the first table to the corresponding cell in the second.

This is my current code: (all comments welcome ofcourse)

Private Sub cb_compare_Click()
   Dim my_workbook As Workbook
   Set my_workbook = ActiveWorkbook
   Dim my_worksheet As Worksheet
   Dim my_array() As String
   Dim my_array_size As Integer
   Dim my_table As ListObject
   
   Dim my_tbl_1 As ListObject
   Dim my_tbl_2 As ListObject
   Dim cell_1 As Range
   ' Slimming down the cell reference code
   Dim lp_cell_1 As Range
   Dim lp_cell_2 As Range
   
   ' Variables to mark differences between cell values
   Dim my_cell_len_1 As Integer
   Dim my_cell_len_2 As Integer
   Dim my_str_1 As String
   Dim my_str_2 As String
   ' Variable to loop through strings to compare
   Dim i As Integer
   
   ' Check if the two tables/listobjects are different
   If Cbo_tbl_list_01.SelText = Cbo_tbl_list_02.SelText Then
      MsgBox "No relevance in comparing the table with itself.", vbOKOnly, "Selection mistake made."
   Else
      'Assign table object with selected table name to variable without referencing the sheet. (cleverly copied)
      Set my_tbl_1 = Range(Cbo_tbl_list_01.SelText).ListObject
      Set my_tbl_2 = Range(Cbo_tbl_list_02.SelText).ListObject
      
      ' TODO check if table 2 has more or less columns
      ' TODO check if table 2 has more or less rows
      ' TODO Check if table 2 column labels are the same
      
      'Dim c As Long
      'With my_tbl_1
         'c = 0
         'For Each cell_1 In .Range
         'c = c + 1
         'Debug.Print "cell_1 " & cell_1.Address, "my_tbl_1 " & .Range(c).Address
         'Next
      'End With
      'With my_tbl_2
         'c = 0
         'For Each cell_1 In .Range
         'c = c + 1
         'Debug.Print "cell_1 " & cell_1.Address, "my_tbl_2 " & .Range(c).Address
         'Next
      'End With
      
      Debug.Print "========= NEW RUN ========="
      Application.ScreenUpdating = False
      For Each cell_1 In my_tbl_1.Range
      
      'Setting Loop_Cell_Objects: Slimming down the cell reference code throughout the code
      Set lp_cell_1 = my_tbl_1.Range(cell_1.Row - my_tbl_1.Range.Row + 1, cell_1.Column - my_tbl_1.Range.Column + 1)
      Set lp_cell_2 = my_tbl_2.Range(cell_1.Row - my_tbl_1.Range.Row + 1, cell_1.Column - my_tbl_1.Range.Column + 1)
      
         ' Cell_1.address is absolute, however my_tbl_1.range(cell_1.row, cell_1.column) starts at (1,1) hence the coordinates of cell_1.address need to be
         ' corrected back for its own offset (and 1 added because counting starts at 1, not at 0): see above for application.
         If lp_cell_1.Value = lp_cell_2.Value Then
            Debug.Print "Match. "
         Else
            Debug.Print "Mismatch " & cell_1.Address, "my_tbl_1 " & lp_cell_1.Value
            Debug.Print "Mismatch " & cell_1.Address, "my_tbl_2 " & lp_cell_2.Value
            'Get length of strings in cells to compare
            my_cell_len_1 = Len(lp_cell_1.Value)
            my_cell_len_2 = Len(lp_cell_2.Value)
            'Get cell values in variables for comparison
            my_str_1 = lp_cell_1.Value
            my_str_2 = lp_cell_2.Value
            'Compare string char by char and mark red.
            For i = 1 To my_cell_len_1
               If i > my_cell_len_2 Or UCase(Mid(my_str_1, i, 1)) <> UCase(Mid(my_str_2, i, 1)) Then
               Debug.Print "Correction: " & my_str_1, my_str_2
                  lp_cell_1.Characters(Start:=i, Length:=1).Font.ColorIndex = 3
               End If
               ' Ended character comparison and marking
            Next i
            'Ended looping through characters
            'Check and mark the other table too.
            For i = 1 To my_cell_len_2
               If i > my_cell_len_1 Or UCase(Mid(my_str_2, i, 1)) <> UCase(Mid(my_str_1, i, 1)) Then
               Debug.Print "Correction: " & my_str_2, my_str_1
                  lp_cell_2.Characters(Start:=i, Length:=1).Font.ColorIndex = 3
               End If
               ' Ended character comparison and marking
            Next i
            ' Ended looping through characters
         End If
         ' Ended cell comparison
      Next
      ' Ended looping through cells
      Application.ScreenUpdating = True
   End If
   'Ended
End Sub

I'll see if I can do something more with your suggestion, as it seems to be simpler by default.

Thanks,
Erik

 

[John_w]

I think you can use the single indexing method to change:

      For Each cell_1 In my_tbl_1.Range
      
      'Setting Loop_Cell_Objects: Slimming down the cell reference code throughout the code
      Set lp_cell_1 = my_tbl_1.Range(cell_1.Row - my_tbl_1.Range.Row + 1, cell_1.Column - my_tbl_1.Range.Column + 1)
      Set lp_cell_2 = my_tbl_2.Range(cell_1.Row - my_tbl_1.Range.Row + 1, cell_1.Column - my_tbl_1.Range.Column + 1)

with a new cellIndex variable, to:

      Dim cellIndex As Long
      cellIndex = 0
      For Each cell_1 In my_tbl_1.Range
        cellIndex = cellIndex + 1
        
        'Setting Loop_Cell_Objects: Slimming down the cell reference code throughout the code
        Set lp_cell_1 = my_tbl_1.Range(cellIndex)
        Set lp_cell_2 = my_tbl_2.Range(cellIndex)

 

[hombibi]

Ok, had to look again at CPearson's explanation, coin dropped now.

The cell index method works well, it is even some 10% faster, and it is much easier to interpret (...).
Obviously this requires both tables to be of exact same dimensions, but as that was a prereq already that's fine.
This now provides an option to reverse the objects I introduced to reduce the cell reference clutter I had.

Thanks John_w, that's been very helpfull, and while at it I learned something.