VBA build formula as per data length column D

[motilulla]

Hello,</SPAN></SPAN>

Column D data vary; it can be 50.000 rows or can be 50 rows</SPAN></SPAN>

In the cells F5, G5 & H5 I got following formulas as per Column d data length </SPAN></SPAN>
F5 =SUM(F6:F42)</SPAN></SPAN>
G5 =SUM(F6:F42)</SPAN></SPAN>
H5 =SUM(F6:F42)</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J
1
2
3
4
5						2.091	0	0
6				A		295
7				A		155
8				A		127
9				A		142
10				A		106
11				A		130
12				A		116
13				A		82
14				A		70
15				A		58
16				A		59
17				A		75
18				A		62
19				A		75
20				A		59
21				A		58
22				A		61
23				A		60
24				A		54
25				A		41
26				A		26
27				A		19
28				A		36
29				A		38
30				A		30
31				A		27
32				A		30
33				A
34				A
35				A
36				A
37				A
38				A
39				A
40				A
41				A
42				A
43
44
Sheet1

My query is it possible that the formula F5, G5, H5 could be modified by VBA as per data length Column D</SPAN></SPAN>

For example if column D data last row are 20.000, so the formula in F5, G5, H5 can be changed as below by the VBA</SPAN></SPAN>

F5 =SUM(F6:F20000)</SPAN></SPAN>
G5 =SUM(F6:F20000)</SPAN></SPAN>
H5 =SUM(F6:F20000)</SPAN></SPAN>


Thank you all</SPAN></SPAN>

Excel 2000</SPAN></SPAN>
Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Sorry I got mistake current formulas are filled in the cells F5, G5 & H5 are as follow </SPAN></SPAN>
F5 =SUM(F6:F42)</SPAN></SPAN>
G5 =SUM(G6:G42)</SPAN></SPAN>
H5 =SUM(H6:H42) as per data length column D last row 42</SPAN></SPAN>

But when column D filled till last row 20000, I need formula in the cells F5, G5 & H5 could be changed as follow</SPAN></SPAN>
F5 =SUM(F6:F20000)</SPAN></SPAN>
G5 =SUM(G6:G20000)</SPAN></SPAN>
H5 =SUM(H6:H20000)</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Fluff]

Try
=SUM(OFFSET(F6,,,LOOKUP(2,1/(D:D<>""),ROW(D:D))))

 

[motilulla]

Try
=SUM(OFFSET(F6,,,LOOKUP(2,1/(D:D<>""),ROW(D:D))))

Hello Fluff, formula return #¡NUM! </SPAN></SPAN>

Ok any way I think what I need sum formula which is entered in cell F5 "= SUM(F6:F42)" because column D has data entered till row 42.</SPAN></SPAN>

So now if I enter data in the Column till rows 20000, I need a macro that can change the formula only in the cell F5 This---> F5 "= SUM(F6:F42)" To This One---> "= SUM(F6:F20000)"</SPAN></SPAN>

May this help</SPAN></SPAN>

Thank you

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[Fluff]

How about

Sub AddFormula()
   Dim Lr As Long
   Lr = Range("D" & Rows.Count).End(xlUp).Row
   Range("F5").Formula = "=sum(F6:F" & Lr & ")"
End Sub

 

[motilulla]

How about

Sub AddFormula()
   Dim Lr As Long
   Lr = Range("D" & Rows.Count).End(xlUp).Row
   Range("F5").Formula = "=sum(F6:F" & Lr & ")"
End Sub

Fluff, This is it!! What I needed Perfect Solution</SPAN></SPAN>

Thank you so much for your help</SPAN></SPAN>

Kind regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[Fluff]

Glad to help & thanks for the feedback