Thread: VBA to compare 2 sheets/workbooks

Hey everyone, thanks in advance for the help.

So I have 2 workbooks, and 1 worksheet in each one. Workbook1 is used for calculations and has all kinds of formulas and things and works as it should. I want to use workbook2 to check the results of the formulas from workbook1. Workbook2 will simply be a list of all of the expected values for the formulas from workbook1. I need to write a simple macro that will simply go down the list in workbook2 and see if the numbers match the values that should be present in each respective cell in workbook1. All I need is a simple pass-fail: if they match, move on to the next one. If they dont, then stop the macro and spit out an error message.

I'd imagine this could probably be solved with some sort of if-then and/or do-while loop?

I've never written a macro on Excel before, so that's why I'm asking for help. Any advice would be very much appreciated. Thank you again, in advance.

--BA Patterson.

Why VBA? A regular formula can compare the cells for you.

In Workbook2, add a Sheet2. In cell A1 enter something like this:

=IF(Sheet1!A1 = [Workbook1.xls]Sheet1!A1, "", ADDRESS(ROW(),COLUMN())

Now copy that cell down and across as needed...the ADDRESSES of the problem cells will appear.

Check every cell? Or check a particular column? Any restriction here at all on the comparison range?

This will simply compare every cell that has a formula in it:

Option Explicit

Sub CompareResults()
Dim WB1 As Workbook:    Set WB1 = Workbooks("Test.xls")
Dim WB2 As Workbook:    Set WB2 = ThisWorkbook
Dim ws1 As Worksheet:   Set ws1 = WB1.Sheets("Sheet1")
Dim ws2 As Worksheet:   Set ws2 = WB2.Sheets("Sheet1")
Dim cell As Range

    For Each cell In ws1.UsedRange.SpecialCells(xlCellTypeFormulas)
        If ws2.Range(cell.Address).Value <> cell.Value Then
            MsgBox "Error in cell " & cell.Address
            GoTo ExitHandler
        End If
    Next cell

ExitHandler:
    Set WB1 = Nothing
    Set WB2 = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing
End Sub

Edit the references at the top.

Generic "process all cells in the range" goes row by row. Just play try it out and you'll spot the pattern.

This technique will process all cells with formulas in them. All other cells are ignored.

I think your numbers are not exactly the same. Excel is very precise, but we are not and our perceptions frequently let us down.

Let's see some of these formulas... I'll bet you've got values that look like 1-2 decimal values or even whole numbers for the display, but the actual calculated result is not the same as is being shown.

Post a sample workbook so we can look at the problem together.

This is only one workbook. The macro is designed to compare cells in two workbooks, the workbook the macro is in and another.

According to your edits in the macro, you set WB1 = to ExampleSheet.xls, but that is the name of the macro'd workbook, which is supposed to be WB2.

WB1 and ws1 need to be one workbook.
WB2 (ThisWorkbook) and ws2 need to be the workbook with the macro in it.