CODEWITHSUNDEEP
vbaexcelsumoffset
terny
terny

Reputation: 21

fill down formula using vba to set starting cell dynamic

lastRow44 = Cells(Rows.Count, "A").End(xlUp).Row
LastRow3 = Worksheets("Temp").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

Range("A" & LastRow3).End(xlDown).Offset(0, 11).Formula = "=Sum(M50:M" & lastRow44 & ")"

I am trying to modify vba code to be more dynamic. I would like to set sum calculation more dynamic. So I am trying something like Range("A" & LastRow3).End(xlDown).Offset(0, 11).Formula = "=Sum(("M" & LastRow3).End(xlDown).Offset(0, 11) & lastRow44 & ")" to define starting cell as M50 automatically. However it is not work as what I intended. Is any way to modify code to make sum calculation of starting cell dynamic?

Thank you!

Upvotes: 0

Views: 279

Answers (2)

Brandon Barney
Brandon Barney

Reputation: 2392

Try changing

lastRow44 = Cells(Rows.Count, "A").End(xlUp).Row
LastRow3 = Worksheets("Temp").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row

to 

lastRow44 = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
LastRow3 = Worksheets("Temp").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

Also, I am not sure what you are trying to accomplish with

Range("A" & LastRow3).End(xlDown).Offset(0, 11).Formula = _
"=Sum(("M" & LastRow3).End(xlDown).Offset(0, 11) & lastRow44 & ")"

What your formula is doing is first setting to the lastrow that you defined, and then searching downward (as if you hit CTRL + down-arrow). If this is not what you intend, try removing the ".END(xlDown" portion of both.

Lastly, if you know you are using an offset of 11, why not set it to use "M" instead of A, and simply not offset?

Upvotes: 1

VBA Pete
VBA Pete

Reputation: 2666

How about something like that:

lastRow44 = Cells(Rows.Count, "A").End(xlUp).Row
For x = 50 To LastRow3
Range("A" & x).Formula = "=Sum(""M""" & x & "": M "" & lastRow44 & ")"
Next x

Upvotes: 0

Related Questions