Solved

VBA Excel 2000 - Compare headers

Posted on 2011-03-25
12
267 Views
Last Modified: 2012-05-11
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,
HeaderCheck.xls
0
Comment
Question by:csehz
  • 7
  • 5
12 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
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

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
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

Open in new window

0
 
LVL 30

Expert Comment

by:SiddharthRout
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

Open in new window

Sid
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:csehz
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

by:SiddharthRout
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

Open in new window


Sid
0
 
LVL 1

Author Comment

by:csehz
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

Open in new window

0
 
LVL 1

Author Comment

by:csehz
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

by:SiddharthRout
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

by:SiddharthRout
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

by:csehz
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

by:
SiddharthRout earned 500 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

Open in new window


Sid
0
 
LVL 1

Author Closing Comment

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

Thanks just again
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question