Summation based on each chunk(blank cells)

Eugene0913

New Member
Joined
Jul 15, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
    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
Joined
May 28, 2005
Messages
50,509
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 15, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
May 28, 2005
Messages
50,509
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 15, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
Joined
May 28, 2005
Messages
50,509
Office Version
  1. 365
Platform
  1. Windows
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.
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top