I would like to compare the formulas of two exactly the same work sheets in one file.( The results of the work sheets will differ because of different input.) If there are non equal of omitted formulas a message (e.g. "fault") must show.

=IF(Sheet1!A1 <> Sheet2!A1, "Fault", "")

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

DerbyDad03

Thank you for your reply. Can you please do this test? Enter a simple formula (e.g. =C1+D1) in Sheet1!A1now enter on Sheet1 a number 5 in C1 and a 6 in D1, the result in A1 will be 11. Enter the same simple formula (=C1+D1) on Sheet2!A1and now enter on Sheet2 a number 7 in C1 and a 8 in D1which will give a result of 15 in Sheet2!A1. Now write your formula (=IF(Sheet1!A1 <> Sheet2!A1, "Fault", "") in Sheet3!A1 and it will give a message of "fault" because 11 does not equal 15 which is correct, even though the formulas (=C1+D1) in cell A1 on both Sheets 1 and 2 are exactly the same. How can I compare the formula in Sheet1!A1 to Sheet2!A1? so that my answer on sheet3!A1 will be "same" and should it differ (say, =C1+E1) then "fault"

Regards Chris27

I'm confused. Are you saying that you want a way to compare the 2

formulasthemselves and not theresultsof the 2 formulas?The only way to do that is with a User Defined Function written in VBA.

Place this code in a Standard VBA module and then enter =FormulaCheck() in any cell.

Function FormulaCheck() Application.Volatile If Sheets(1).Range("A1").Formula <> _ Sheets(2).Range("A1").Formula Then FormulaCheck = "Fault" Else FormulaCheck = "Same" End If End Function

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

DerbyDad03

Thank you for the code to check/compare a formula on sheet1 and sheet2. I have entered the "=FormulaCheck" in cell A1 on sheet3

The code do check cell A1 of sheet 1 and 2 correct. Can you please modify/extend the code "FormulaCheck" to also check/compare ALL the cells on sheets 1 and 2.

Regards

Chris27

If you wanted to compare all of the cells in the 2 worksheets, you should have asked for that. Since we can't read minds, all we can do is answer the question that is asked, which was to compare a single cell. Comparing all of the formulas in a worksheet is a bit more difficult and doesn't lend itself to using a UDF. I suggest a macro can be run against Sheets 1 & 2, comparing the sheets cell by cell over the UsedRange and checking the formulas.

Since I assume that some cells will contain formulas and others will not, the code only returns Fault or Same if at least one of the 2 cells it is comparing contains a formula. For example:

- If Sheet1!B3 and Sheet2!B3 both contain constants or text or anything else that is not a formula, the code will return "No Formula".

- If both cells contain a matching formula the code will return "Same".

- If both cells contain formulas but they don't match, or if only

oneof the 2 cells contains a formula, then the code will return "Fault".The code also assumes that that the UsedRange in Sheet1 is at least as big as the UsedRange in Sheet2 since it uses the UsedRange in Sheet1 to determine how many cells to compare.

Note: The first line of this code will clear the contents of Sheet3 so that you get fresh data each time you run it. Make sure that you don't have anything in Sheet3 that you want to keep.

As always, I suggest that you test this code in a backup copy of your workbook since macros can not easily be undone.

Sub ChkFormulas() Sheets(3).Cells.ClearContents For Each myCell In Sheets(1).UsedRange If Sheets(1).Range(myCell.Address).HasFormula Or _ Sheets(2).Range(myCell.Address).HasFormula Then If Sheets(1).Range(myCell.Address).Formula <> _ Sheets(2).Range(myCell.Address).Formula Then Sheets(3).Range(myCell.Address) = "Fault" Else Sheets(3).Range(myCell.Address) = "Same" End If Else: Sheets(3).Range(myCell.Address) = "No Formula" End If Next End Sub

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

DerbyDad03

Thank you for your help, I really appreciated it, I have successfully compared the formula on two sheets. My problem is solved.

My VBE is set to option explicit and I had to insert a Dim Mycell as Variant to declare the variable, I dont know if this is correct, but i got it to work with this.

Can you modify the "chkformula" to use the sheet names that I have eg the sheet name with the correct formula is April-10 and I want to compare my other sheets Jun-10 ,Jul-10, Aug-10 ect. to April-10 in succession?

Regards

Chris27

Since you keep adding requirements to this task, I'm going to politely decline your request to modify the code (again). As I mentioned in my last response, it's cumbersome to have to keep modifying code, setting up test workbooks, testing the code, posting it, explaining it, etc.

Here's a suggestion: The next time you post a question is a Help forum such as this one, don't post your requirements in little pieces. Put all of your requirements in one post so that a complete answer can be provided the first time.

Imagine if you were a carpenter and someone asked you to build them a cabinet. Then, after you cleaned up the work area and put all your tools away, they asked you add drawers to the cabinet. Then, after you cleaned up the work area and put all your tools away, they asked you add glass in the doors. That's kind of what you are doing when you tell us your requirements bit by bit after a bunch of work has already been done.

I think this would be a great learning opportunity for you and a chance to expand your VBA knowledge. All you need to do add a For-Next loop in the code to check each sheet in sequential order.

Good Luck!

Click Here Before Posting Data or VBA Code --->How To Post Data or Code.

DerbyDad03

Apology for any irritation, I have learned a lot.

Thank you so much for your time and effort, your patience, advice and even still a proposal, I really appreciate it.

I will work on the For-Next loop and will get it right.

Regards

Chris27

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History