Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.
Option Explicit
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim boolMatch As Boolean
Dim X As Long, Y As Long
Dim ws1Value, ws2Value
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
boolMatch = True
Do Until ws1Value = ""
Do Until ws2Value = ""
If ws2Value <> ws1Value Then
boolMatch = False
Exit Do
End If
X = X + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
X = 0
Y = Y + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
If boolMatch = True Then MsgBox "Range is Identical" Else _
MsgBox "Range is not Identical"
End Sub
Option Explicit
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim boolMatch As Boolean
Dim X As Long, Y As Long
Dim ws1Value, ws2Value
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
boolMatch = True
Do Until ws2Value = ""
If ws2Value <> ws1Value Then
boolMatch = False
Exit Do
End If
Y = Y + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
If boolMatch = True Then MsgBox "Range is Identical" Else _
MsgBox "Range is not Identical"
End Sub
Sid
Option Explicit
Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim boolMatch As Boolean
Dim X As Long, Y As Long
Dim ws1Value, ws2Value
Set ws1 = Sheets("Sheet1")
Set ws2 = Sheets("Sheet2")
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
If ws1Value = "" Or ws2Value = "" Then boolMatch = False Else _
boolMatch = True
Do Until ws2Value = ""
If ws2Value <> ws1Value Then
boolMatch = False
Exit Do
End If
Y = Y + 1
ws1Value = ws1.Range("A1").Offset(X, Y).Value
ws2Value = ws2.Range("A1").Offset(X, Y).Value
Loop
If boolMatch = True Then MsgBox "Range is Identical" Else _
MsgBox "Range is not Identical"
End Sub
Sub RangeCompare()
Dim Range1 As Range
Dim Range2 As Range
Set Range1 = Worksheets("Sheet1").Range("A1:F1")
Set Range2 = Worksheets("Sheet2").Range("A1:F1")
If "Range1" = "Range2" Then MsgBox "Ranges are identical" Else MsgBox "Ranges are not identical"
End Sub
Sub RangeCompare()
Dim Range1 As Range, Range2 As Range
Dim aCell As Range
Set Range1 = Sheets("Sheet1").Range("A1:F1")
Set Range2 = Sheets("Sheet2").Range("A1:F1")
r1 = Range1.Row
c1 = Range1.Column
r2 = Range2.Row
c2 = Range2.Column
'~~> Check if they contain same number of Columns
If Range1.Columns.Count <> Range2.Columns.Count Then
MsgBox "Range is not identical"
Exit Sub
End If
'~~> Check if they contain same number of Rows
If Range1.Rows.Count <> Range2.Rows.Count Then
MsgBox "Range is not identical"
Exit Sub
End If
Cols = Range1.Columns.Count
'~~> Check if the values are same
For i = 1 To Cols
If Sheets("Sheet1").Cells(r1, c1) <> Sheets("Sheet2").Cells(r2, c2) Then
MsgBox "Range is not identical"
Exit Sub
End If
c1 = c1 + 1
c2 = c2 + 1
Next i
MsgBox "Range is identical"
End Sub
Open in new window
Sid