Solved: comparing two workbooks

[grichey]

Here's the situation. I have 2 work books which have a number of sheets in them. All the formulas should be identical across the same given sheet in either workbook (ie sheet 1 should contain the same formulas in the same cells as any other sheet 1 in another workbook). Anyone have any ideas how to check? Values could all be different based on variables entered but the formulas should be the same.

Maybe it's just Monday, but I'm coming up dry here.
Thanks

[charlesa920]

Sounds like you know they are the same right now but need to verify they stay that way even if one of those pesky users tries something intuitive, like type ing in a cell.

why not just lock them up? I saw a thread about locking them with code

[grichey]

I've got the code to lock them already in place. It works great. The problem is I have more than one person with access to the password who may have changed something in 1 particular sheet which albeit clicking all 50,000 cells would be hard to locate.

[charlesa920]

what happens when you copy and paste the correct forumla into a cell that
A) has no data
B) has data
C) pesky user changed forumla
D) Did I miss any possiblities?

[charlesa920]

you might consider doing a macro on selection change with the range = the cells with formula which gives a msgbx and doesn't let them change it

[lucas]

I've got the code to lock them already in place. It works great. The problem is I have more than one person with access to the password who may have changed something in 1 particular sheet which albeit clicking all 50,000 cells would be hard to locate.

I know this is like closing the barn door after the horse has escaped but you should always have a backup of files like this especially if there are 50,000 cells involved....:doh:

[mikerickson]

This is a rather brute force method of checking if two sheets have the same formulas. (I haven't tested formulas that refer to third workbooks.)


Sub test()
Dim goodSheet As Worksheet
Dim suspectSheet As Worksheet
Dim goodFormulas As Range
Dim suspectFormulas As Range
Dim goodArea As Range, goodCell As Range

Set goodSheet = Workbooks("knownGoodBook.xls").Sheets(1)
Set suspectSheet = Workbooks("suspectBook.xls").Sheets(1)

Set goodFormulas = goodSheet.Cells.SpecialCells(xlCellTypeFormulas)
Set suspectFormulas = suspectSheet.Cells.SpecialCells(xlCellTypeFormulas)

If goodFormulas.Cells.Count <> suspectFormulas.Cells.Count Then
MsgBox "Formula miscount": Exit Sub
End If

For Each goodArea In goodFormulas.Areas
For Each goodCell In goodArea
If suspectSheet.Range(goodCell.Address).Formula <> goodCell.Formula Then
MsgBox "Formula mismatch at " & goodCell.Address
End If
Next goodCell
Next goodArea

MsgBox "done"
End Sub

5,000 sheets? Do you have a known good workbook? If so, rather than detecting changes, you could write a macro to clear all formula from the suspect book, write good formulas in their cells, preserving the constants in the suspect book (other than overwriting them with formulas if things have been moved around).

[grichey]

Yes I have a good known workbook. Not 5000 sheets. 25 work books @ 25 worksheets/wkbk. Thx for the idea on just overwriting. Is there a way to check for a formula vs a constant? Maybe just check if cell begins with =?