# Summation based on each chunk(blank cells)

#### Eugene0913

##### New Member
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

#### Attachments

• rate 2.PNG
64.2 KB · Views: 6

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### Peter_SSs

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

VBA Code:
``````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

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

VBA Code:
``````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

##### MrExcel MVP, Moderator
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
GLMN
1styleRate
2187920.074074
3187930.111111
4187960.222222
5187950.185185
6187960.222222
7187950.185185
8
93192250.438596
10319240.070175
11319280.140351
12319290.157895
13319260.105263
14319250.087719
15
16444430.272727
17444420.181818
18444410.090909
19444450.454545
20
Sum blocks
Cell Formulas
RangeFormula
N2:N19N2=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

##### New Member
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
GLMN
1styleRate
2187920.074074
3187930.111111
4187960.222222
5187950.185185
6187960.222222
7187950.185185
8
93192250.438596
10319240.070175
11319280.140351
12319290.157895
13319260.105263
14319250.087719
15
16444430.272727
17444420.181818
18444410.090909
19444450.454545
20
Sum blocks
Cell Formulas
RangeFormula
N2:N19N2=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

##### MrExcel MVP, Moderator
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.

Replies
8
Views
81
Replies
14
Views
228
Replies
13
Views
284
Replies
4
Views
190
Replies
7
Views
262

1,141,002
Messages
5,703,663
Members
421,310
Latest member
CindaH

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back