Using VBA to summarize calculations on cells in different rows in a very large workbook

[jamiect]

Hi All,

I have had a lot of success with your help on the site. I have been pointing my coworkers here to learn how to solve their own problems as well, and I'm learning a LOT, so thank you all!

Today I have a new problem I cannot work out how to solve cleanly, but I am new to learning functions and loops so it may be pretty straightforward:

I have a list of accounts and transactions with four columns in each row. "Transaction#". "Paid". "Owing". "Notes". there may be up to 3000 or 4000 rows.

I get updates for each client each day with an unknown number of rows and I want to build a vba function which will help check and summarize each sheet. Here's what I'm trying to make it do for a sheet with 4 rows.

1001 100 150 notes (my calculation)
1002 150 250 notes (my calculation)
1003 250 400 notes (my calculation)
1004 400 500 notes (my calculation)

My calculation in each case is to copy and paste a formula manually to check that:
C1 is the same as B2, and C2 is the same as B3, etc.

But I find that this is slow and errors in copying can cause me huge headaches as I have to find the problems manually.

So, I am making a macro using vba to start on Row 2 and check that the B value of each row is the same as the C value of the previous row and return a Zero if there is no difference. (We start on row 2 so it has a previous value to compare to!). When I get to a blank line, I then have to stop so the last value is not nonsense, (it won't have a "B" value to compare with).

In case where a calculation does not match, I have to record the value so that I can go back and check the notes. I guess it would have to complete the check, no mater whether there are numerous mismatches or not... but give me a list of the bad rows to check

So my output msgbox would look something like:

"No matching errors"

or maybe a list:

"problems with Column B in transactions 1001, 1003, 1008, 100x..."

I hope this makes sense.

I will probably plan to make a button on the toolbar so I can load an xlsx file and then run the function right away to see how much checking i need to do!

Does anyone have a good idea how to get this working?

I figured out how to look at each cell in a single column, stopping at a blank row, but that is as far as I got.

Thanks!

Jamie

[JBeaucaire]

I believe you can get what you want with some simple formulas, no need for the VBA overhead.

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Make sure your desired results are demonstrated, mock them up manually and point them as the cells you're trying to automate. Remember to desensitize the data if necessary.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic