Autofill formula based on another column (VBA)

[feni1388]

Hello....

Does anyone have any idea how to fix the autofill formula below?
Currently I'm using this code but then it recognises B30 as the last row.
Is it possible to make it to autofill only until B20 instead?
The list is dynamic, can be shorter or longer so I can't set it to B3:B20.

Thank you in advance.

 Range("F3").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]/RC[-2]"
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F" & Range("B" & Rows.Count).End(xlUp).Row)
    Columns("F:F").Select
    Selection.Style = "Percent"

Book1
	A	B	C	D	E	F
1			Data
2		PIC	Total sales qty	Total sales amount	Profit	Profit (%)
3		Total	187,145	473,302,637	105,049,998	22%
4		A	10,484	58,263,615	12,696,865	22%
5		B	13,455	46,572,561	7,317,808	16%
6		C	7,467	41,699,470	8,462,400	20%
7		D	7,416	36,529,923	6,720,201	18%
8		E	6,928	36,495,283	8,205,262	22%
9		F	33,125	36,168,129	13,561,277	37%
10		G	6,104	32,617,616	7,784,118	24%
11		H	5,840	32,315,123	6,290,065	19%
12		I	5,078	28,652,594	6,546,681	23%
13		J	4,956	25,176,056	5,717,270	23%
14		K	4,393	23,531,087	5,226,979	22%
15		L	3,552	19,407,747	4,082,574	21%
16		M	3,601	19,205,216	4,275,475	22%
17		N	3,486	18,915,590	4,184,614	22%
18		O	2,700	14,638,442	3,261,903	22%
19		P	68,514	2,806,565	620,322	22%
20		Q	46	307,620	96,184	31%
21						#DIV/0!
22						#DIV/0!
23						#DIV/0!
24						#DIV/0!
25						#DIV/0!
26		PIC	Total sales qty	Total sales amount	Profit	#VALUE!
27	This year	SQ	39,617	39,265,855	16,734,749	43%
28	Last year	SQ	33,125	36,168,129	13,561,277	37%
29						#DIV/0!
30	Now	Diff	-6,492	-3,097,726	-3,173,472	102%
Sheet1

Cell Formulas
Range	Formula
F3:F30	F3	=E3/D3

 

[footoo]

You could change your formula to : =IF(D3="","",E3/D3)

 

[feni1388]

You could change your formula to : =IF(D3="","",E3/D3)

That's true.... I also thought of that before but there's title in the middle and other formula too at the bottom.
So if I thought if anyone knew the solution to that formula, it'd be best.
If not, then I just do what you suggested and change the other part.
Thank you.

 

[footoo]

That's true.... I also thought of that before but there's title in the middle and other formula too at the bottom.
So if I thought if anyone knew the solution to that formula, it'd be best.
If not, then I just do what you suggested and change the other part.
Thank you.

If the rows at the bottom always number ten, then :

 Selection.AutoFill Destination:=Range("F3:F" & Range("B" & Rows.Count).End(xlUp).Row-10)

If the rows do not always number 10 but the bottom 5 rows are always occupied like your sample data, then :

Selection.AutoFill Destination:=Range("F3:F" & Range("B" & Rows.Count).End(xlUp).Row - 5)

With the second option you would need to change the formula.

 

[feni1388]

If the rows at the bottom always number ten, then :

 Selection.AutoFill Destination:=Range("F3:F" & Range("B" & Rows.Count).End(xlUp).Row-10)

If the rows do not always number 10 but the bottom 5 rows are always occupied like your sample data, then :

Selection.AutoFill Destination:=Range("F3:F" & Range("B" & Rows.Count).End(xlUp).Row - 5)

With the second option you would need to change the formula.

That's perfect. Thank you so much.