Comparing Cells Between Worksheets, Range Doesn't Start on A1

Question

Leveraging the post here, I was able to build a VBA macro to compare cells between worksheets to suit my purposes. However, until now, I didn't test it with a range that doesn't start at A1.

When giving a range like B1:X50, I encounter an issue where the LBound of the column dimension of the sheet array is 1 while the column at the start of the given range is 2 (B). Here's a portion of the code I'm working with:

'Define the range to be checked
strRangeToCheck = Summary_ws.Range("I2").Value

'Store the range to be checked on each sheet in an array
varSheetA = New_Data_ws.Range(strRangeToCheck)
varSheetB = Old_Data_ws.Range(strRangeToCheck)

'Clear current highlighted cells on data tabs
New_Data_ws.Cells.Interior.Color = xlNone
Old_Data_ws.Cells.Interior.Color = xlNone

'Initialize counter
Counter = 0
Counter_rng.Value = Counter

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
    For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
        On Error GoTo Err_Handler
        If IsError(New_Data_ws.Cells(iRow, iCol).Value) Or IsError(Old_Data_ws.Cells(iRow, iCol).Value) Then
            Counter = Counter + 1
            Error_Flag = True
            Call Flag_Changes
        ElseIf varSheetA(iRow, iCol) = varSheetB(iRow, iCol) And _
                Len(varSheetA(iRow, iCol)) = Len(varSheetB(iRow, iCol)) Then
                'Cells are identical and lengths match
                'Do Nothing
        Else 'Cells are different
            Counter = Counter + 1 'increment counter
            Error_Flag = False
            Call Flag_Changes
        End If
    Next iCol
Next iRow

I'm able to evaluate the strRangeToCheck to determine if it starts at column A or not. Based on that determination, would it then be possible to manually set the LBound of the sheet to match the starting column in strRangeToCheck?

this line would create a problem for you since it is only one cell `strRangeToCheck = Summary_ws.Range("I2").Value` Your range is always one cell or it could be more than one cell? — Ibo, Feb 28 '17 at 19:09
That cell contains a string provided by the user, like A1:Z100. If they give an invalid range, an error is thrown. — kschindl, Feb 28 '17 at 20:05

score 1 · Accepted Answer · edited Sep 21 '17 at 21:17

In answer to the question "would it then be possible to manually set the LBound of the sheet to match the starting column", the answer is no. Range.Value will always return an array with a lower bound of 1 - Excel is responsible for creating the returned array, and you can't change that behavior via VBA (and wouldn't want to even if you could for various other reasons).

The solution is to create a Range from the passed address as an intermediate step before you pull the .Value's into your arrays:

'Store the range to be checked on each sheet in an array
Dim newRange As Range
Dim oldRange As Range
Set newRange = New_Data_ws.Range(strRangeToCheck)
Set oldRange = Old_Data_ws.Range(strRangeToCheck)
varSheetA = newRange.Value
varSheetB = oldRange.Value

Then instead of using Worksheet.Cells in the loop, use Range.Cells. If you need to convert that to worksheet addressing, you find the offset from A1 from the first Row in the Range. Using your example of B1:X50:

Dim example As Range
Set example = Sheet1.Range("B1:X50")
Debug.Print example.Columns(1).Column   '<-- column 2
Debug.Print example.Rows(1).Row         '<-- row 1

Note that in your code above, you're only using the worksheet indexing on a single line (although I have no clue what you're doing in Call Flag_Changes):

If IsError(New_Data_ws.Cells(iRow, iCol).Value) Or IsError(Old_Data_ws.Cells(iRow, iCol).Value) Then

You don't need to do this at all. New_Data_ws.Cells(iRow, iCol).Value is the exact same thing as varSheetA(iRow, iCol). That's why you pull it into an array, right? Just replace that line with this:

If IsError(varSheetA(iRow, iCol)) Or IsError(varSheetB(iRow, iCol)) Then

Finally, you didn't include the declarations for iRow and iCol, but judging by the Hungarian notation if they're declared as Integer you need to change them to Long to avoid overflow errors. If they are declared as Long, that's yet another argument against not using Hungarian notation to begin with...

Thanks for your help! I was able to get it to work. Also, I was completely ignorant to the Hungarian notation. That being said, `iRow` and `iCol` were defined as `Long`, but I will update the code to comply with naming standards. I'll post my updated code. — kschindl, Feb 28 '17 at 20:08

kschindl · Answer 2 · 2017-02-28T21:15:07.797

I'm not sure if this is the most elegant way to implement the advice, but here's working code, thanks to Comintern:

'Store the range to be checked on each sheet in an array
Set newRange = New_Data_ws.Range(strRangeToCheck)
Set oldRange = Old_Data_ws.Range(strRangeToCheck)
varSheetA = newRange.Value
varSheetB = oldRange.Value

'Variables used to track difference between array start and given range start
row_diff = newRange.Rows(1).Row - 1
col_diff = newRange.Columns(1).Column - 1

'Loop through given range
For rngRow = newRange.Rows(1).Row To newRange.Rows.Count + row_diff
    arrRow = rngRow - row_diff 'increment row of array being evaluated
    For rngCol = newRange.Columns(1).Column To newRange.Columns.Count + col_diff
        arrCol = rngCol - col_diff 'increment column of array being evaluated
        On Error GoTo Err_Handler
        If IsError(varSheetA(arrRow, arrCol)) Or IsError(varSheetB(arrRow, arrCol)) Then
            Counter = Counter + 1
            Error_Flag = True
            Call Flag_Changes
        ElseIf varSheetA(arrRow, arrCol) = varSheetB(arrRow, arrCol) And _
                Len(varSheetA(arrRow, arrCol)) = Len(varSheetB(arrRow, arrCol)) Then
                'Cells are identical and lengths match
                'Do Nothing
        Else 'Cells are different
            Counter = Counter + 1 'increment counter
            Error_Flag = False
            Call Flag_Changes
        End If
    Next rngCol
Next rngRow

In my Flag_Changes sub, I'm just doing things like highlighting the different cells on either sheet, printing the address of that cell, printing the values of each cell, and the difference (if the cells are numeric).

Comparing Cells Between Worksheets, Range Doesn't Start on A1

2 Answers2