VBA to Add In Formlae?

[Silvermini63]

I am working on the 80/20 rule and I wish to be able to get I am thing what will need to be some VBA to put the formulae in for me.

Not sure if the below example has formulae in it but at
N2=D2/$D$13, N3=D3/$D$13 etc etc
O2=N2, O3=N3+O2, O4=N4+O3 etc etc

I then need to apply this at the beginning of each new customer so could I get a VBA code that can find blank cell say in Column A and then apply these formulae changing the locked reference to the subtotalled Quantity in Column D

So the next customer would be the following.
N14=D14/$D$25, N15=D15/$D$25 etc etc
O14=N14, O15=N15+O14, O16=N16+O15 etc etc

Excel Workbook
	A	B	C	D	G	H	I	J	K	L	M	N	O
1	Customer	Item	Description	Qty	Heading	Heading	Heading	Heading	Heading	Heading	Heading	% Sales	Cumuative %
2	XXXXXXX	XXXXX	Product 1	273								30.7%	30.7%
3	XXXXXXX	XXXXX	Product 2	126								14.2%	44.8%
4	XXXXXXX	XXXXX	Product 3	126								14.2%	59.0%
5	XXXXXXX	XXXXX	Product 4	60								6.7%	65.7%
6	XXXXXXX	XXXXX	Product 5	50								5.6%	71.3%
7	XXXXXXX	XXXXX	Product 6	49								5.5%	76.9%
8	XXXXXXX	XXXXX	Product 7	48								5.4%	82.2%
9	XXXXXXX	XXXXX	Product 8	41								4.6%	86.9%
10	XXXXXXX	XXXXX	Product 9	40								4.5%	91.3%
11	XXXXXXX	XXXXX	Product 10	40								4.5%	95.8%
12	XXXXXXX	XXXXX	Product 11	37								4.2%	100.0%
13				890	-	-	#DIV/0!
14	XXXXXXX	XXXXX	Product 1	10								16.7%	16.7%
15	XXXXXXX	XXXXX	Product 2	9								15.0%	31.7%
16	XXXXXXX	XXXXX	Product 3	8								13.3%	45.0%
17	XXXXXXX	XXXXX	Product 4	8								13.3%	58.3%
18	XXXXXXX	XXXXX	Product 5	8								13.3%	71.7%
19	XXXXXXX	XXXXX	Product 6	5								8.3%	80.0%
20	XXXXXXX	XXXXX	Product 7	5								8.3%	88.3%
21	XXXXXXX	XXXXX	Product 8	4								6.7%	95.0%
22	XXXXXXX	XXXXX	Product 9	1								1.7%	96.7%
23	XXXXXXX	XXXXX	Product 10	1								1.7%	98.3%
24	XXXXXXX	XXXXX	Product 11	1								1.7%	100.0%
25				60	-	-	#DIV/0!
1.10.16.5 ver (Mr Excel post)

Excel 2010

Cell Formulas
Range	Formula
D13	=SUM(D2:D12)
D25	=SUM(D14:D24)
N2	=D2/$D$13
N3	=D3/$D$13
N4	=D4/$D$13
N5	=D5/$D$13
N6	=D6/$D$13
N7	=D7/$D$13
N8	=D8/$D$13
N9	=D9/$D$13
N10	=D10/$D$13
N11	=D11/$D$13
N12	=D12/$D$13
N14	=D14/$D$25
N15	=D15/$D$25
N16	=D16/$D$25
N17	=D17/$D$25
N18	=D18/$D$25
N19	=D19/$D$25
N20	=D20/$D$25
N21	=D21/$D$25
N22	=D22/$D$25
N23	=D23/$D$25
N24	=D24/$D$25
O2	=N2
O3	=N3+O2
O4	=N4+O3
O5	=N5+O4
O6	=N6+O5
O7	=N7+O6
O8	=N8+O7
O9	=N9+O8
O10	=N10+O9
O11	=N11+O10
O12	=N12+O11
O14	=N14
O15	=N15+O14
O16	=N16+O15
O17	=N17+O16
O18	=N18+O17
O19	=N19+O18
O20	=N20+O19
O21	=N21+O20
O22	=N22+O21
O23	=N23+O22
O24	=N24+O23
G13	=SUM(G2:G12)
G25	=SUM(G14:G24)
H13	=SUM(H2:H12)
H25	=SUM(H14:H24)
I13	=(G13-H13)/G13*100
I25	=(G25-H25)/G25*100

 

[Fazza]

Maybe?

Sub test()
 
  Const lngColDescription As Long = 3
  Const lngColQty As Long = 4
  Const lngColPercent As Long = 12
  Const lngColCumulative As Long = 13
 
  Dim lngRowFirst As Long
  Dim rngBlank As Range
 
  lngRowFirst = 2
  For Each rngBlank In Columns(lngColDescription).Cells.SpecialCells(xlCellTypeBlanks).Areas
    Cells(lngRowFirst, lngColPercent).Resize(rngBlank.Row - lngRowFirst).FormulaR1C1 = "=RC" & lngColQty & "/R" & rngBlank.Row & "C" & lngColQty
    Cells(lngRowFirst, lngColCumulative).Resize(rngBlank.Row - lngRowFirst).FormulaR1C1 = "=SUM(R" & lngRowFirst & "C" & lngColPercent & ":RC" & lngColPercent & ")"
    lngRowFirst = rngBlank.Row + 1
  Next rngBlank
 
End Sub

 

[Fazza]

PS. You might want to erase previous formulas/entries too. F

 

[Silvermini63]

That worked really well the only thing is it put them in columns "L" & "M" and not in "N' & "O" like I need them, what do I need to change in the code to move them over?

other than that thank you so much

 

[Fazza]

Sorry about that.

'instead of the constant declarations
Sub test()
 
  Const lngColDescription As Long = 3
  Const lngColQty As Long = 4
  Const lngColPercent As Long = 12
  Const lngColCumulative As Long = 13
 
 
'change the last two
Sub test()
 
  Const lngColDescription As Long = 3
  Const lngColQty As Long = 4
  Const lngColPercent As Long = 14
  Const lngColCumulative As Long = 15

Or you might have the code search for the text to identify the columns if they might move but the headers will stay the same.

 

[Silvermini63]

Thanks for that and yep worked well.