Solved

# VBA Excel 2000 - Compare headers

Posted on 2011-03-25
Medium Priority
278 Views
Dear Experts,

Could you please have a look at the attached file, it contains Sheet1 and Sheet2 with certain header texts in the first row.

I know that the Excel easily can compare cell values whether those are equal like whether A1=A2, but is it possible in VBA to do this for ranges whether two ranges are the same?

At the certain example the question would be whether A1:F1 on Sheet1 is equal to A1:F1, meaning that the cells exactly are in the same order and containing the same text?

thanks,
0
Question by:csehz
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 5

LVL 30

Expert Comment

ID: 35214668
Try this

``````Sub Sample()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim boolMatch As Boolean

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
``````

Sid
0

LVL 30

Expert Comment

ID: 35214685
Just amended the code to declare the variables. This will check if A1:F1 of Sheet1 is identical to A1:F1 of Sheet2.

Sid

Code Used

``````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
``````
0

LVL 30

Expert Comment

ID: 35214786
In Case you want to work with ranges like A1:F1 and A1:G1 then use this code

``````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
0

LVL 1

Author Comment

ID: 35214876
Sid thanks the code, generally seems working but if on Sheet2 the cell A1 is empty, also in that case says that Range is Identical which is not true
0

LVL 30

Expert Comment

ID: 35214913
Try this

``````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
``````

Sid
0

LVL 1

Author Comment

ID: 35215035
Just also considering that why the attaced Code always brings that the Ranges are not identical?
``````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
``````
0

LVL 1

Author Comment

ID: 35215080
Sid checking the last code, if the Sheet2 E1 cell content is empty, in that case still brings that Range is identical
0

LVL 30

Expert Comment

ID: 35215105
Yes that is because it check A-E. I think I understood what you want.

Let me amend the code.

Sid
0

LVL 30

Expert Comment

ID: 35216185
Sorry got tied up in the other thread.

Quick question. You want this code to simply check the headers in a row. right? And you do not want to specify a fix range (except the starting cell)?

Sid
0

LVL 1

Author Comment

ID: 35231946
Sid sorry I had the chance to check your comment only now,

Yes the target would be that the cells exactly are in the same order and containing the same text.

Maybe as in the logic in the below not working code, so defining two ranges and compare

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
0

LVL 30

Accepted Solution

SiddharthRout earned 2000 total points
ID: 35245210
Ok. Going as per your last post, since you are specifying Range1 and Range2 then I guess this is what you want?

``````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
``````

Sid
0

LVL 1

Author Closing Comment

ID: 35257388
Sid thanks very much, this code works for all scenarios which I was able to imagine.

Thanks just again
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Excel can be a tricky bit of software to get your head around. Whilst youâ€™ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to dâ€¦
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to usâ€¦
###### Suggested Courses
Course of the Month10 days, 19 hours left to enroll