Forum Discussion

Bronze Contributor

May 18, 2018

Solved

compare two worksheets if identical

In the same workbook: I have one worksheet named pc1 , another named pc I want to compare both - if identical (exactly the same) - then exit the sub routine the syntax below is not working - pls ...

excel

Matt Mickle

May 22, 2018

Lorenzo-

Try this sub procedure. I have commented it for better understanding.

Sub TestFormulasAndValues()

    Dim pc As Worksheet
    Dim pc1 As Worksheet
    Dim ColLp As Integer
    Dim RowLp As Integer
    
    'Set worksheets
    Set pc = Worksheets("pc")
    Set pc1 = Worksheets("pc1")
    
    'Cycle through Rows 1 to 11
    For RowLp = 1 To 11
        'Cycle through columns 1 to 5 (A-E)
        For ColLp = 1 To 5
            'Compare Values
            If pc.Cells(RowLp, ColLp).Value = pc1.Cells(RowLp, ColLp).Value Then
                If pc.Cells(RowLp, ColLp).Formula = pc1.Cells(RowLp, ColLp).Formula Then
                    'Do Nothing.... value and formula match...
                Else
                    'Formulas don't match Exit the sub procedure
                    MsgBox "Formulas do not match in " & pc.Cells(RowLp, ColLp).Address
                    Exit Sub
                End If
            Else
                'Values don't match exit the sub procedure
                MsgBox "Values don't match in " & pc.Cells(RowLp, ColLp).Address
                    Exit Sub
            End If
        Next ColLp
    Next RowLp

End Sub

Lorenzo Kim

Bronze Contributor

May 21, 2018

Mr Mickle

Thank you for your reply.

I tried the attached file - it worked.

will it work if there are more than 2 worksheets?

how would sub test() be written to compare just the worksheets "pc1" and "pc" out of many worksheets in the workbook?

many thanks

Matt Mickle

Bronze Contributor

May 22, 2018

Lorenzo-

Try this sub procedure. I have commented it for better understanding.

Sub TestFormulasAndValues()

    Dim pc As Worksheet
    Dim pc1 As Worksheet
    Dim ColLp As Integer
    Dim RowLp As Integer
    
    'Set worksheets
    Set pc = Worksheets("pc")
    Set pc1 = Worksheets("pc1")
    
    'Cycle through Rows 1 to 11
    For RowLp = 1 To 11
        'Cycle through columns 1 to 5 (A-E)
        For ColLp = 1 To 5
            'Compare Values
            If pc.Cells(RowLp, ColLp).Value = pc1.Cells(RowLp, ColLp).Value Then
                If pc.Cells(RowLp, ColLp).Formula = pc1.Cells(RowLp, ColLp).Formula Then
                    'Do Nothing.... value and formula match...
                Else
                    'Formulas don't match Exit the sub procedure
                    MsgBox "Formulas do not match in " & pc.Cells(RowLp, ColLp).Address
                    Exit Sub
                End If
            Else
                'Values don't match exit the sub procedure
                MsgBox "Values don't match in " & pc.Cells(RowLp, ColLp).Address
                    Exit Sub
            End If
        Next ColLp
    Next RowLp

End Sub

Lorenzo Kim
Bronze Contributor
May 22, 2018
Mr Mickle

The VBA code worked perfectly!!
Thank you very very much.