Recoding macro to add all cells above a target cell until there is a blank row.

Hi I have a worksheet with a process I am trying to automate to save time. So far I am able to record simple macros to get most of the job done but I cannot work out how to make a macro for the second step. I first click on a cell that has information in it and have made a shortcut to insert a blank row. The second step (the step i need an answer for) needs to sum all the cells above it until it comes to a blank row and provide the total in a cell three cells to the right of the targeted cell.

I have tried recording one using relative referencing but as the number of cells above the target varies each time i use the macro it returns an incorrect result. Please see the attached worksheet for an example of the data.

In the example i have already added rows at 63 and 72 to give an idea of the difference there can be in the amount of rows above the targeted cell and i need a macro that will total the cells in the column above B63 and provide the answer in the cell E63.

Then i need to be able to use the same macro to start at B72 and for it to total all the numbers in the column above it until it reaches the blank row and provide the answer in cell E72

I am only new to using macros so if someone could suggest how to do this with the record function that would be great.

Thanks in advance

I think this should work:

Please Login or Register  to view this content.

Thank you for looking into it. Because I've only used the record function to make macros before can you please just give me a quick rundown on how to use the coding you've provided to make a macro?

Sure,

Open up the VBA viewer by pressing ALT + F11. Click on Insert on the top ribbon and select Module. Paste in the code in the new window that opens, and then close the viewer. Run the code from your worksheet by pressing ALT + F8 and selecting the macro.

Here's your sheet with the code pasted in.

Thanks that seems to work but not how I need it to because i explained myself poorly. Between using the macro to find the sum of the column till a blank i need to do other processes that will change some of the data and so i will explain the entire process that i need all the macros to do together. First i need to click a cell in the worksheet, then i need it to insert a new row (this is the first macro i recorded), then i need to total the numbers in the column above the blank row until there is another blank and provide the answer in a cell three to the right of the original (the macro you coded), then i need to calculate the answer for $H$1 minus the answer given for the column sum(second macro i recorded). I then need to copy the cell under the starting cell and paste the value only (not formula) to the cell three to the right of it (third macro i recorded), i then need to go back to the cell underneath the starting cell (the one we just copied) and have the value there be the value three cells to the right of it minus the value in the cell we started at and the create another blank row underneath the original cell position (fourth macro i recorded).

You see i need to split the data up into 100 even groups based on the total of numbers in the second column and so to do that i need it to spit certain entries into two different rows that have a values equal to the original value.

To see what i mean please see the attached workbook where each sheet shows what happens in every step.

So in the end I either need a macro that will do step 2 and not interfere with the rest of the macros or one big macro that does all of the steps itself so i can just click the required cell and do it with one button

Another:

Please Login or Register  to view this content.

That was close but on the second run through i.e when i go to sort the next goruping it omits the top value, possibly because i put an inserted row.

see attached file to understand what i mean.

First which suggestion/Post are you referring too. Second, if you are referring to Post #6, I'm not sure I understand.

Apologies I was referring to post #6, if you open up the spreadsheet you can see that I've applied the macro you coded for me but this is all in an effort to automate a procedure to separate data into 100 different even groups (see post #5). To do this I need to split certain rows into two rows that have the same value for column A but the column B value is divided between the two new rows. In the attachment in #7 you can see that i've applied all the macros I outlined in post #5 at the first point that needs to be split B 63, but when I keep going down the column to repeat the process at the next split point the macro you gave me omits the top value before the gap in the rows I've made between the first and second group (it totals B66-B89 but misses B 65 which was split the first time around and the remainder put into the second group.

I hope that explains it a bit better.

How are you determining your split points?

1 percent of the total observations column

So, just for clarity, you want to insert rows to break up your data into 100 equally-sized groups? And then continue on to steps 2-5?

yes that's correct

I think I've understood. Try this:

Hi thanks, for your continued efforts.

I've tried that one but i seem to be getting negative figures in the second column which shouldn't be possible, Did i execute it incorrectly?

Also once again I wasn't specific enough with details, when I said that the "Splits" should be based on the total in column B. so i need groups that are based on the amount of observations (column B).

In my actual data the total of column B is 305503, so every group needs to have 3055.03 observations in it.

This means that where the B column gets split every time there are 3055.03 observations in it, the line where the split occurs gets changed to to entries with the top one having its column B value changed so that it and the cells above it total 3055.03 and the lower one (the one after the split) has the remaining amount. When a split occurs the amount in column B above the split and below the split should total to make the value that was origionally there.

I hope that helps.

Thanks again

Originally Posted by PatrickDC

Also once again I wasn't specific enough with details, when I said that the "Splits" should be based on the total in column B. so i need groups that are based on the amount of observations (column B).

In my actual data the total of column B is 305503, so every group needs to have 3055.03 observations in it.

The reason it's splitting after 10 rows and not 3055 is because your data in the sample only has 1000 obs. If you were to run the same code with 305503 obs it would split after 3055 rows.

Originally Posted by djdjdj

The reason it's splitting after 10 rows and not 3055 is because your data in the sample only has 1000 obs. If you were to run the same code with 305503 obs it would split after 3055 rows.

I do understand this but it is splitting the data based on the amount of rows and not the numbers in those rows. So in my actual data set needs to be split every time that column B (the observations column) totals 3055.03, which due to the nature of the numbers involved every time a split occurs a new row needs to be made to account for the splitting of an existing row into two rows with the same A column value, but separate column b values. e.g in the example data the total in column b is 130136 (the sum of the values in the column, not the amount of rows). So I need to end with 100 equal groups where the total in column be is 1301.36 (one percent of the total of column b). so the first split should occur would be between rows 38 and 39.

Hope that helps

Hi,

I'm not sure if this is working like you want it to, but this version splits the groups based on values. So every time the group sum becomes larger than 1% of the total, a split is made. But, there are still some negative values created because of the realvtively large values in some of your cells. Have a look anyway:

Its getting closer . It shouldn't return any negative values though. Each time there is a break the total column B in each group should total to give 1301.36 which is 1%, which also means that each value on either side of the breaks should have values with decimal places.