VBA to compare same cell on seperate sheet then loop through range

[gordsky]

Hi,

I have a wb that has several sheets but for ease lets assume it has only 2.
What I am looking to do is compare ws1.Range("A1") with ws2.Range("A1") .

Easy enough in itself but I want the code to do is then loop through a range("A2:D53") comparing each ws1 cell with the same cell on ws2.

Ive managed to do it by writing following code

Set ws1 = Sheets("Input")
Set ws2 = Sheets("B2P")
For i = 2 To 53

Set Rng1 = ws1.Range("A" & i)
Set Rng2 = ws2.Range("A" & i)
If Rng1 = Rng2 Then
'do something
End if
Next i
End Sub

I have then repeated the code changing "A" to "B" and so forth to last column.
Is there a way to do this without code repetition.?

Any help much appreciated

 

[johnnyL]

See if this works for you:

Sub CompareTwoRanges()
'
    Set ws1 = Sheets("Input")
    Set ws2 = Sheets("B2P")
'
'   Getting the two range set for comparing
    Set Rng1 = ws1.Range("A2:D53")
    Set Rng2 = ws2.Range("A2:D53")
'
'   Disabling screen updates
    Application.ScreenUpdating = False
'
'   Checking whether Rng1 and Rng2 contains value
    If Rng1 Is Nothing Or Rng2 Is Nothing Then Exit Sub
'
'   Getting count of number of rows and columns in Rng1
    With Rng1
        LR1 = .Rows.Count
        LC1 = .Columns.Count
    End With
'
'   Looping through all the columns and rows in the range
    For c = 1 To LC1
        For r = 1 To LR1
'           Getting value from particular cell from both the ranges
            CellValue1 = Rng1.Cells(r, c)
            CellValue2 = Rng2.Cells(r, c)
        
'           Comparing value of cell from both ranges, column by column
            If CellValue1 <> CellValue2 Then
' Do somethings if the two cells don't match   <---- \/ \/ \/ \/
MsgBox "Sheet1 Cell value of " & CellValue1 & " vs Sheet2 Cell value of " & CellValue2
            End If
        Next r
    Next c


'   Enabling screen updates
    Application.ScreenUpdating = True
End Sub

 

[gordsky]

Hi johnnyL,

That works perfectly and thank you for breaking it down into understandable chunks.
I wonder if you could expand further on the below section of code:

Getting count of number of rows and columns in Rng1
With Rng1
LR1 = .Rows.Count
LC1 = .Columns.Count
End With

' Looping through all the columns and rows in the range
For c = 1 To LC1
For r = 1 To LR1

As we know the fixed range is "A2:D53" could the first bit be left out and it be written as:

For c = 1 To 4
For r = 2 To 53

I can see the benefit of the count for much larger ranges etc but just curious to understand a bit better?

 

[Fluff]

Another way, would be

Sub gordsky()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   With Sheets("B2P")
      For Each Cl In Sheets("Input").Range("A2:D53")
         If Cl.Value = .Range(Cl.Address) Then
            MsgBox "Match"
         Else
            MsgBox "Input " & Cl.Value & " B2P " & .Range(Cl.Address) & " don't match"
         End If
      Next Cl
   End With
End Sub

 

[johnnyL]

As we know the fixed range is "A2:D53" could the first bit be left out and it be written as:

For c = 1 To 4
For r = 2 To 53

I can see the benefit of the count for much larger ranges etc but just curious to understand a bit better?

You could do that, but the code that you want to eliminate is the code the auto calculates the 4 and 53 in your example.
In other words, with that 'auto calculating' code deleted, if you were wanting to change the ranges being checked, you would also have to manually change the 4 and 53.

 

[johnnyL]

@gordsky, after thinking about it, your "For r = 2 to 53" would NOT work.