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](http://stackoverflow.com/questions/5387929/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. — , Sep 18 '13 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, Sep 18 '13 at 14:24

Gary's Student · Answer 1 · 2013-09-18T14:41:17.870

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 — user1948797, Sep 18 '13 at 14:22

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

1 Answers1