Code to generate a "Success" Pop Up Box after macro runs

[onthegreen03]

Hi all. I am looking for some simple coding which I can add to one of my macros that does the following. Sum up cells D109:F122 and cells D43:F46 and checks that the two totals match. I would then like a pop up box that states either "Success, DOT run successfully" if the two sums match, or "DOT did not run successfully" if the sums are different. Is this something that be pasted in the thread, that I can copy/paste into one of my existing macros? The existing macro below runs a simple Goal Seek; I want to add the sum check and pop up box to this.

 

[dreid1011]

Give this a try:

If WorksheetFunction.Sum(Range("D43:F46")) = WorksheetFunction.Sum(Range("D109:F122")) Then
    MsgBox "Success, DOT run successfully"
Else
    MsgBox "DOT did not run successfully"
End If

 

[onthegreen03]

Thank you for the quick reply. I added the code and it ran without errors, but it returned "DOT did not run successfully" even though the sums are exactly the same. Strange. Any idea why that would happen? One set of numbers are all links to a different tab in the model, while the other contains both hardcoded inputs (3 cells) and formulas (6 cells) linking to the hardcoded numbers. Not sure if that is why?

 

[Joe4]

Are you sure that you do not have decimal portions that your format may be hiding (the formulas work off the underlying values, not the displayed values)?
Even if you do, it could be due to the famous "floating point arithmetic issue" (see here: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps).

The way to get around that is to add the ROUND formula to your SUM calculation, and round the SUM to a set number of decimal points.

 

[dreid1011]

Thank you for the quick reply. I added the code and it ran without errors, but it returned "DOT did not run successfully" even though the sums are exactly the same. Strange. Any idea why that would happen? One set of numbers are all links to a different tab in the model, while the other contains both hardcoded inputs (3 cells) and formulas (6 cells) linking to the hardcoded numbers. Not sure if that is why?

What if you enter the SUM formulas into the spreadsheet and compare the values? Are the values integers or decimals?

Edit: what Joe said.

 

[onthegreen03]

I did that exact thing after I replied to you. I added sum formulas to the bottom of the sheet and both ranges are equal to 190. I set the cell format to "Number" with 5 decimal places and the two sums are the same.

 

[onthegreen03]

Are you sure that you do not have decimal portions that your format may be hiding (the formulas work off the underlying values, not the displayed values)?
Even if you do, it could be due to the famous "floating point arithmetic issue" (see here: Floating-point arithmetic may give inaccurate result in Excel - Microsoft 365 Apps).

The way to get around that is to add the ROUND formula to your SUM calculation, and round the SUM to a set number of decimal points.

Thanks, let me try this.

 

[onthegreen03]

Thanks both. When I added the ROUND function to both results the macro worked as it should. Appreciate the help!

 

[Joe4]

You are welcome. Glad we were able to help!

I set the cell format to "Number" with 5 decimal places and the two sums are the same.

As I said, changing the number format of the cell has no impact on the calculations. They will still include all digits, even those not shown.