excel vba sheet comparison using vba in excel and store result in a sheet?

Question

I need to dynamically select the range of two excel sheets and do a row by row comparison and print it in a report sheet something like true or false using excel VBA macro.. pls help.. VBA Macro to compare all cells of two Excel files this link was helpful, but i wanted to dynamically select the range and also need to print some TRUE/FALSE in comparison sheet.

I exactly wanted to load the sheets to variant array and then loop through them for fast performance of the code.

Note - please assume that both the sheets needs to be compared contains same no of rows and are sorted.

For iRow = LBound(varSheetA, 1) To UBound(varSheetA, 1)
    For iCol = LBound(varSheetA, 2) To UBound(varSheetA, 2)
        If varSheetA(iRow, iCol) = varSheetB(iRow, iCol) Then
            ' Cells are identical.
            ' i want to go to the exact cell in Comparison sheet and type TRUE Else
            ' Cells are different.
            ' i want to go to the exact cell in Comparison sheet and type FALSE
        End If
    Next iCol
Next iRow

So your questions is a duplicate of VBA Macro to compare all cells of two Excel files. What is your specific problem? Show your code and tell us what is not working and what you've already tried. — user2140173
– user2140173, Commented Sep 18, 2013 at 13:39
im unable to find navigate through the Comparision sheet and fill each of the cell with TRUE/FALSE , since the variable i use is variant.. — user1948797
– user1948797, Commented Sep 18, 2013 at 14:24

Gary's Student · Accepted Answer · 2013-09-18 14:41:17Z

Here is a sample. The sample code only records the array(i,j) coordinates of the mismatches in the comparison tab. You should modify it to record any other information you need:

Sub CompareSheets()
    Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
    Dim rComp As Range, addy As String
    Dim I As Long, J As Long, K As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Set s3 = Sheets("comparison")
    s1.Select
    Set rComp = Application.InputBox(Prompt:="Select range", Type:=8)
    addy = rComp.Address
    ary1 = rComp
    ary2 = s2.Range(addy)
    K = 1
    For I = LBound(ary1, 1) To UBound(ary1, 1)
        For J = LBound(ary1, 2) To UBound(ary1, 2)
            If ary1(I, J) = ary2(I, J) Then
            Else
                s3.Cells(K, 1) = I
                s3.Cells(K, 2) = J
                K = K + 1
            End If
        Next J
    Next I
End Sub

EDIT:

In response to your comment this version will fill the comparison sheet with TRUEs and FALSEs:

Sub CompareSheets2()
    Dim s1 As Worksheet, s2 As Worksheet, s3 As Worksheet
    Dim rComp As Range, addy As String
    Dim I As Long, J As Long, K As Long
    Set s1 = Sheets("Sheet1")
    Set s2 = Sheets("Sheet2")
    Set s3 = Sheets("comparison")
    s1.Select
    Set rComp = Application.InputBox(Prompt:="Select range", Type:=8)
    addy = rComp.Address
    ary1 = rComp
    ary2 = s2.Range(addy)
    ary3 = s3.Range(addy)
    K = 1
    For I = LBound(ary1, 1) To UBound(ary1, 1)
        For J = LBound(ary1, 2) To UBound(ary1, 2)
            If ary1(I, J) = ary2(I, J) Then
                ary3(I, J) = "TRUE"
            Else
                ary3(I, J) = "FALSE"
            End If
        Next J
    Next I
    s3.Range(addy) = ary3
End Sub

Thanks for the reply, but i exactly wanted to print 'TRUE/FALSE against each cell in Comparison sheet

Collectives™ on Stack Overflow

excel vba sheet comparison using vba in excel and store result in a sheet?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related