Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Range Totals After VBA Manipulation
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Range Totals After VBA Manipulation
It looks like that would work if you placed
"Application.WorksheetFunction." in front of the SUM statement. On Aug 29, 11:38 am, PJFry wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare Range Totals After VBA Manipulation
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").R ange("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 "Jason" wrote: It looks like that would work if you placed "Application.WorksheetFunction." in front of the SUM statement. On Aug 29, 11:38 am, PJFry wrote: 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want to compare totals and flag those less than or more than | Excel Discussion (Misc queries) | |||
Need some help with more effective range manipulation | Excel Programming | |||
For better Performance in VBA for Excel - Strings manipulation OR Objects manipulation | Excel Programming | |||
Range manipulation | Excel Programming | |||
Range Manipulation Question | Excel Programming |