Compare Range Totals After VBA Manipulation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

PJFry - 29 Aug 2007 17:38 GMT

I have a workbook in which users dump in data, run a macro and use the
result. The macro itself works great, but the users need to go the original
data to confirm that the totals still match.

Here is the process:
Users dump data into the 'Data' tab (original, huh?)
Run Macro
Compare the total value on the 'Output' tab to the 'Data' tab to make they
match.

Here is what I am thinking, but don't know how to code:

Dim x As Double
Dim y As Double

x = SUM(Sheets("Data").Range("F2:F50000"))
y = SUM(Sheets("Output").Range("Q2:Q50000"))

If x<>y Then
MsgBox "Totals do not match"
End If

Thoughts?

PJ

Reply to this Message

Jason - 29 Aug 2007 18:40 GMT

It looks like that would work if you placed
"Application.WorksheetFunction." in front of the SUM statement.

> I have a workbook in which users dump in data, run a macro and use the
> result. The macro itself works great, but the users need to go the original
[quoted text clipped - 21 lines]
>
> PJ

Reply to this Message

PJFry - 29 Aug 2007 18:56 GMT

That did it. I also had to add a rounding function to x and y. It was
showing a variance of something like 5.31654-11.

x As Double
y As Double

Here is what I ended up with:
x = Application.WorksheetFunction.Sum(Sheets("Data").Range("AI:AI"))

y = Application.WorksheetFunction.Sum(Sheets("Upload").Range("C:C"))

If Round(x, 2) <> Round(y, 2) Then
MsgBox "Totals between the Data tab and the Upload tab do not match.",
vbOKOnly, "Total Error"
MsgBox "The file creation has been halted. Please correct the error and
rerun.", vbOKOnly
Exit Sub
End If

> It looks like that would work if you placed
> "Application.WorksheetFunction." in front of the SUM statement.
[quoted text clipped - 24 lines]
> >
> > PJ

Reply to this Message