Comparing Formulas Between Worksheets

[John Claussen]

I deal with Workbooks/Worksheets in which I need to compare the previous
version of a WB/WS with the newer version, particularly for differences in
formulas. The Worksheet layouts from one version to the next are the same
(contents of the physical rows and cols), but when the formula changes for a
particular cell, I would like to show the difference on a 3rd worksheet.

For Example:

I have the following input WS's that I would like to analyse for formula
differences that I want to display in a third WS (Sheet3)...

Sheet1 with Cell A1's Formula=SUM(B1,B2)
Sheet2 with Cell A1's Formula=SUM(B1,B2,B3)

And I would like to produce Sheet3...

Cell FormulaSheet1 FormulaSheet2
A1 SUM(B1,B2) SUM(B1,B2,B3)

Any generic algorithm is appreciated. Thank you.


John Claussen

 

[keepITcool]

John

Following should be a good beginning.
if you want to see different Values in addition to the different
formulas you'll need to comment out the 'inner' if/then
that checks for .hasformula


I often use a dictionary cause it's convenient and fast.
and much easier than continuously redimming an array..

you could also write to the "report range" directly
but I prefer the intermediate storage for flexibility

When you have many differences:
speed of the writing of the differences
can be improved by turning off calc, screenupdating etc.
Option Explicit

Sub DocDiffs()
Dim rng1 As Range, rng2 As Range
Dim cel As Range
'needs reference to "Microsoft Scripting Runtime"
Dim dic As Scripting.Dictionary

Set rng1 = Worksheets(1).Range("A1:Z1000")
Set rng2 = Worksheets(2).Range("A1:Z1000")

If rng1.Rows.Count <> rng2.Rows.Count Or _
rng1.Columns.Count <> rng2.Columns.Count Then
MsgBox "Range have different Size. Aborting"
Exit Sub
End If

'Initialize the dictionary
Set dic = New Dictionary

'Compare the cells. Add diff to dictionary
For Each cel In rng1
With rng2.Cells(cel.Row, cel.Column)
'if formulas are different
If StrComp(cel.FormulaR1C1, .FormulaR1C1, vbTextCompare) <> 0 Then
'if either is not a constant...
If cel.HasFormula Or .HasFormula Then
dic.Add .Address, _
Array(.Address, cel.FormulaLocal, .FormulaLocal)
End If
End If
End With
Next cel

'Now write the diffs to a new workbook
Dim rngR As Range, itm As Variant, nRow&
Set rngR = Workbooks.Add(xlWBATWorksheet).Worksheets( _
1).Range("A1:C1")
rngR.Parent.Cells.NumberFormat = "@"
For Each itm In dic.Items
nRow = nRow + 1
If nRow > 65536 Then Set rngR = rngR.Offset(, 3): nRow = 1
rngR.Rows(nRow).Value = itm
Next itm


End Sub


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


John Claussen wrote :