For every blank cell , sum those above it

[Felipebaby]

Hi, I wondered if anyone could help with the below
The spreadsheet below has 3 columns . IN the 3rd there are totals for each group of accounts.
I need a VBA macro that goes in and finds the blank cell and sums the amounts above that .

The macro I am using either sums those above completely or sums the 6 above when in some case there are 7 rows to be added.

A AA 50.00
A AA 50.00
A AA 25.00
A AA 40.00
A AA 20.00
A AA 10.00

B BB 11.00
B BB 15.00
B BB 2.00
B BB 3.00
B BB 4.00
B BB 5.00
B BB 6.00

C CC 1.50
C CC 2.60
C CC 3.60
C CC 5.00
C CC 10.00
C CC 15.00
C CC 20.00
C CC 25.00
C CC 30.00
C CC 35.00
C CC 100.00
C CC 1,000.00


Macro I am using now (that doesn't work !)

Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=SUM(INDIRECT(""R1C""&COLUMN()&"":R""& (ROW()-1)&""C""&COLUMN(),FALSE))"
Selection.Font.Bold = True

Thank you very much

[TMS]

Why not use a Pivot Table?

	A	B	C	D	E	F	G
1	Col A	Col B	Col C			Row Labels	Sum of Col C
2	A	AA	50			A
3	A	AA	50			AA	195
4	A	AA	25			A Total	195
5	A	AA	40			B
6	A	AA	20			BB	46
7	A	AA	10			B Total	46
8						C
9	B	BB	11			CC	1247.7
10	B	BB	15			C Total	1247.7
11	B	BB	2			(blank)
12	B	BB	3			(blank)
13	B	BB	4			(blank) Total
14	B	BB	5			Grand Total	1488.7
15	B	BB	6
16
17	C	CC	1.5
18	C	CC	2.6
19	C	CC	3.6
20	C	CC	5
21	C	CC	10
22	C	CC	15
23	C	CC	20
24	C	CC	25
25	C	CC	30
26	C	CC	35
27	C	CC	100
28	C	CC	1,000.00

Regards, TMS

[Keebellah]

I would do like this with VBA

Please Login or Register  to view this content.

The C28 you must change with a lastrow value + 1, the lastrow must always be a blank one.

[Felipebaby]

Thanks every so much keebellah that works nicely !

When you say LR can I set LR as follows;

LR = ActiveSheet.UsedRange.Rows.Count
so

For Each rng In Range("C" & r1 & :LR")

Thankyou also to TMS for the pivot table suggestion , that didn't even figure in my solutions , oops!

[TMS]

Better to use

Please Login or Register  to view this content.

rather than

Please Login or Register  to view this content.

as UsedRange can give you more rows (and columns) than are actually used. This can happen when Rows (and/or Columns) are deleted.

Regards, TMS

[TMS]

If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Keebellah]

TMS's option

Please Login or Register  to view this content.

Is the best one
How ever I would use the C column, that's where the values are.

[TMS]

How ever I would use the C column, that's where the values are.

Shouldn't make a difference given that all the columns are populated in the same rows. If one of the columns had more entries, I'd use that but, in this case, I don't think it is important.

Regards, TMS

[Felipebaby]

Thanks again TMS for all your help, that works nicely

[TMS]

You're welcome. Thanks for the rep.

[jolivanes]

Here is another possibility that might work.

Please Login or Register  to view this content.

[Felipebaby]

I have a very similar query and wondered if I could use this thread again?

The spreadsheet looks like so;

ABC Amount Apples Pears
Apples 20 20
Pears 30 30

Bass
Apples 50 50
Pears 60 60
A Apples 20 20
A Pears 30 30
B Apples 15 15
B Pears 5 5



Charlies
Apples 5 5
Pears 15 15
A Apples 20 20
B Pears 50 50



I want columns and 4 to have totals included , however I cannot look for blanks as there are formulas in Coulmns C and D as follows;

=IF(ISNUMBER(SEARCH("apples",A2)),B2,"")

=IF(ISNUMBER(SEARCH("Pears",A3)),B3,"")

So basically the macro would put sums after every end of Company ;

ABC Amount Apples Pears
Apples 20 20
Pears 30 30
20 30
Bass
Apples 50 50
Pears 60 60
A Apples 20 20
A Pears 30 30
B Apples 15 15
B Pears 5 5

85 95
Charlies
Apples 5 5
Pears 15 15
A Apples 20 20
B Pears 50 50

25 65

Thanks lots .... again

[Keebellah]

I'll try with what you've posted but I suggest you attach the file, it will make it easier than rewiting the formulas because my country settings are ; instead of , as seprator

[Felipebaby]

I'll try with what you've posted but I suggest you attach the file, it will make it easier than rewiting the formulas because my country settings are ; instead of , as seprator

Thanks Keebellah , sorry for the dealy in responding got caught up in something else !
Here is the file

[Keebellah]

Okay, got the file.
Will take a look later today

[Felipebaby]

Thanks again