Macro - sum up, compare, display. Plz help!

[hunsnowboarder]

Hi Everyone! I already got a very big help on this site from someone and I hope this time there will be also someone who can help me.
First of all I am not good at macros that is why I am asking for help.
Here is my problem:
I have a spreadsheet with three sheets („Data”, „20” and „30”). On the Data sheet I have only two rows, and I do not work on this sheet. I work with the sheets 20 and 30. On the sheet 20 I need to sum up the amount of column H. But my problem is that the count of this column (the rows or cells) allways change. Sometimes there are 554 the next day 663 and so on. So I need the macro to sum up the amount of column H two cells under the last cell. I need exactly the same in the sheet 30, but the only difference is that in the sheet 30 the amounts are in the G column (not in H). Finally I need to compare the two summed up amounts (sheet 20 and 30) and they must be equal. So what I would need as macro is that when I run it, it sums up the amounts in both sheets and then in the Data sheet (in the 5th row, cell A) displays the summed up amount and (in cell B) displays OK if the two summed up amounts are equal, or just dislpays ERROR if the summed up amounts are not equal.
I hope you can understand my problem and you can help about it as I am doing this daily at least 2 times, and it would be very good if I could use a macro instead of doing this manually! Thank you for your attention! Thank you in advance!

[hunsnowboarder]

Can't help anyone?? ((

[royUK]

Can you attach an example workbook, remove any sensitive data and zip it?

[hunsnowboarder]

Sure..Thanx a lot. I made some notes in the file. I hope it is understandable. And thank you in advance!!

[royUK]

Zip file appars to be corrupted.

[hunsnowboarder]

Sorry...I don't know what happened to the file..anyway I rezipped it and checked it..this one should work.

thanx again..

[royUK]

Try this

Please Login or Register  to view this content.

[hunsnowboarder]

Hi Roy!

Thanks for your help. I appreciate it. Unfortunatelly there is a problem... when the macro sums up the two amounts - even if they are identical - it writes ERROR and ERROR on the "Data" sheet. If the amounts are the same it should write OK and the amount. Probably there is something wrong with the code..

P.S.: There was also a slight mistake, but I corrected it.

Instead of:

1. With ws2
2. R = .Cells(.Rows.Count, 7).End(xlUp).Offset(2, 0).Row
3. Set rToSum = .Range(.Cells(2, 7), .Cells(.Rows.Count, 8).End(xlUp))
4. d30 = Application.WorksheetFunction.Sum(rToSum)
5. .Cells(R, 7) = d30
6. End With

In line 3. must be: "Set rToSum = .Range(.Cells(2, 7), .Cells(.Rows.Count, 7).End(xlUp))"
I changed the rows count 8 to rows count 7. Anyway this was just a misstype...but the main problem is that when comparing the two amounts the code does not work properly. Please could you help me out with this one?

Thanks a lot again!

[royUK]

You need to clear the previous totals so I have added code to do that. Test this

Please Login or Register  to view this content.

[hunsnowboarder]

Hi Roy!

Thanx a lot again for trying to help. But did you try this macro? Because everything is working fine, but when comparing the two amounts always comes up with ERROR. Even if the two amounts are identical.

[hunsnowboarder]

I really dont know what is the problem with it...but when comparing the two amounts it always displays ERROR.

[royUK]

I think your problem may well be that although the amounts look the same, they are not in fact the same due to decimals. Try deleting the amounts in the relevant columns & typing some dummy numbers in. The code works fine.

You could check the whole number & ignore decimals like this

Please Login or Register  to view this content.

[hunsnowboarder]

Hey Roy! You made it! It works great!! Thanks a lot!!!! )) You are the man!
Thanks again for your efforts!

[royUK]

Glad it helped.