Summation based on each chunk(blank cells)

[Eugene0913]

I would like to do sum based on each chunk, my code is taking the whole column instead. I can't figure how to write a loop that stops at the blank cells.

Sub Button9_Click()


Dim LR As Long
LR = Range("L" & Rows.Count).End(xlUp).row
Range("N2:N" & LR).Formula = "=L2/SUM(L2:L" & LR & ")"
End Sub

 

[Peter_SSs]

Bit of a guess as we don't know what else might come below your sample, but is possibly could be this?

Dim LR As Long
LR = Range("L" & Rows.Count).End(xlUp).Row
Range("N2:N" & LR).Formula = "=IF(L2="""","""",L2/SUMIF(G$2:G$" & LR & ",G2,L$2:L$" & LR & "))"

 

[Eugene0913]

Bit of a guess as we don't know what else might come below your sample, but is possibly could be this?

Dim LR As Long
LR = Range("L" & Rows.Count).End(xlUp).Row
Range("N2:N" & LR).Formula = "=IF(L2="""","""",L2/SUMIF(G$2:G$" & LR & ",G2,L$2:L$" & LR & "))"

Hi Peter thanks for your reply. Unfortunately it is still taking the whole L instead of by chunk.

 

[Peter_SSs]

Then perhaps I have not guessed correctly about your data structure or requirement.
Here is my sample data (columns G & L) and column N is the result of the code I posted.
For example, 0.140351 in cell N11 is the result of 8/57 where 57 is the sum of L9:L14
Would they be the results you would expect for that sample data?

21 07 15.xlsm
	G					L	M	N
1	style					Rate
2	1879					2		0.074074
3	1879					3		0.111111
4	1879					6		0.222222
5	1879					5		0.185185
6	1879					6		0.222222
7	1879					5		0.185185
8
9	3192					25		0.438596
10	3192					4		0.070175
11	3192					8		0.140351
12	3192					9		0.157895
13	3192					6		0.105263
14	3192					5		0.087719
15
16	4444					3		0.272727
17	4444					2		0.181818
18	4444					1		0.090909
19	4444					5		0.454545
20
Sum blocks

Cell Formulas
Range	Formula
N2:N19	N2	=IF(L2="","",L2/SUMIF(G$2:G$19,G2,L$2:L$19))

Perhaps we need a small set of sample data from you with XL2BB so that we can copy and use that data and the expected results (manually entered) with further explanation in relation to that data.

 

[Eugene0913]

Then perhaps I have not guessed correctly about your data structure or requirement.
Here is my sample data (columns G & L) and column N is the result of the code I posted.
For example, 0.140351 in cell N11 is the result of 8/57 where 57 is the sum of L9:L14
Would they be the results you would expect for that sample data?

21 07 15.xlsm
	G					L	M	N
1	style					Rate
2	1879					2		0.074074
3	1879					3		0.111111
4	1879					6		0.222222
5	1879					5		0.185185
6	1879					6		0.222222
7	1879					5		0.185185
8
9	3192					25		0.438596
10	3192					4		0.070175
11	3192					8		0.140351
12	3192					9		0.157895
13	3192					6		0.105263
14	3192					5		0.087719
15
16	4444					3		0.272727
17	4444					2		0.181818
18	4444					1		0.090909
19	4444					5		0.454545
20
Sum blocks

Cell Formulas
Range	Formula
N2:N19	N2	=IF(L2="","",L2/SUMIF(G$2:G$19,G2,L$2:L$19))

Perhaps we need a small set of sample data from you with XL2BB so that we can copy and use that data and the expected results (manually entered) with further explanation in relation to that data.

Yes! correct

 

[Peter_SSs]

Yes! correct

Then if my results are what you would expect and your are not, there must be something different about our data and/or layout. Therefore ...

Perhaps we need a small set of sample data from you with XL2BB so that we can copy and use that data and the expected results (manually entered) with further explanation in relation to that data.