Comparing Forumlas between worksheets

[hammer02]

I have some worksheets that I need to upgrade but would like to have a way of comparing forumlas between the new and old versions to show any differences. These worksheets have very long formulas and it is very painful to check manually.

I've been able to put together (with help from reviewing posts on this list) a macro to run through a given number of rows and columns and compare cells between the two worksheets. The problem is that the comparison is on the value of the cells, not the forumlas. Can someone tell me what I am missing to be able to compare the forumlas?

'Start checking cells in Sheet2, column 1 for mismatches in Sheet1, column1
For ColCount = 1 To 100 'Loop through cells in Sheet2
For RowCount = 1 To 200 'Loop through cells in Sheet1
'If a Sheet1 row matches a Sheet2 row
If Workbooks(&quot;ChipAnalysis1.xls&quot.Worksheets(&quot;Daily Report&quot.Cells(ColCount, RowCount).Value <> _
Workbooks(&quot;ChipAnalysis2.xls&quot.Worksheets(&quot;Daily Report&quot.Cells(ColCount, RowCount).Value _
Then
Worksheets(&quot;CmpDaily Report&quot.Cells(ColCount, RowCount).Interior.ColorIndex = 8
End If
Next 'move to next row
Next 'Move to next column

Thanks
mhamm@wii.com

 

[ilses]

Replace Value with Formula.

Looping through all cells may not be the fastes way to do this. If I can come up with something faster before bedtime, I'll post back.

Ilses

 

[DaleWatson123321]

hammer,

As a &quot;function type&quot; (I use them where possible because it can be MUCH easier and faster in many cases), I would suggest using the &quot;EXACT&quot; function in the following manner:

1) In comparing 2 sheets, create a 3rd sheet where you enter the following formula into cell A1, and copy it for ALL the cells in use in the 2 sheets you are comparing.

=EXACT(Sheet1!A1,Sheet2!A1)

This will produce a &quot;TRUE&quot; or &quot;FALSE&quot; result.

2) To make it easier to locate your &quot;FALSE&quot; results, I would suggest using &quot;Format&quot; - &quot;Conditional Formatting&quot;, wherein you specify &quot;cell value is&quot; - &quot;equal to&quot; - &quot;FALSE&quot;.
Then choose &quot;Format&quot; (still under Conditional Formatting) - choose &quot;Patterns&quot; and a BRIGHT color such as YELLOW.

This option works for BOTH formulas and text.

I hope you find this option useful. Please advise.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[hammer02]

Dale,

thanks for the info. However, when I tried the EXACT formula as you subscribed the result was that the spreadsheets indicated differences based on the values of the cell, not the forumlas. What am I doing wrong?

Thanks,
Mike

 

[SkipVought]

hi,

Here's a procedure that works for formulae.

Sub FindDiscrepancies()
    Dim RowCount As Long, ColCount As Integer, lRow As Long, iCol As Integer
    Dim Frm1, Frm2, Discrepancy(), idx
'--activate first workbook/sheet and assign row & col counts
'  (assuming starting in row 1 col 1
    Sheet2.Activate
    With Cells(1, 1).CurrentRegion
        RowCount = .Rows.Count
        ColCount = .Columns.Count
    End With

    idx = 1
    For lRow = 1 To RowCount
        For iCol = 1 To ColCount
        'switch to wkbk/sht 1
            Sheet2.Activate
            Frm1 = Cells(lRow, iCol).Formula
        'switch to wkbk/sht 2
            Sheet3.Activate
            Frm2 = Cells(lRow, iCol).Formula
        'compare formulae
            If Frm1 <> Frm2 Then
                ReDim Preserve Discrepancy(1 To 2, 1 To idx)
                Discrepancy(1, idx) = lRow
                Discrepancy(2, idx) = iCol
                idx = idx + 1
            End If
        Next
    Next
'activate the report sheet
    Sheet4.Activate
    For lRow = LBound(Discrepancy, 2) To UBound(Discrepancy, 2)
        For iCol = LBound(Discrepancy, 1) To UBound(Discrepancy, 1)
            Cells(lRow, iCol).Value = Discrepancy(iCol, lRow)
        Next
    Next
End Sub

Hope this helps. Skip,
metzgsk@voughtaircraft.com