Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Formulas Between Worksheets
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Comparing Formulas Between Worksheets
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 : 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
comparing from two worksheets | Excel Discussion (Misc queries) | |||
Comparing 2 worksheets | Excel Worksheet Functions | |||
Comparing two worksheets | Excel Discussion (Misc queries) | |||
Comparing Two Worksheets for changes | Excel Discussion (Misc queries) | |||
Comparing 2 worksheets | Excel Programming |