Create a formula using VBA

[swaink]

Hi All

Are you able to assist please

In my spreadsheet I have headers in row 4 from column I to Z

I am looking to put a sum of each column in row 4 and am looking to do this using vba

I have enabled my code to identify the last row to aid me with the formula but am struggling to get the formaula applied across all of the columns

The formulas will start in cell I4 but my formula below is not working

[I4].End(xlRight) = "=Sum(" & Range([I4], & lastrow) & ")"

Any advise is appreciated

Regards

Kevin

 

[Mike Blackman]

Hi,

Try;

Range(Range("I4"), Cells(4, Columns.Count).End(xlToLeft)).Formula = "=SUM(" & Range("I4:I" & lastrow).Address & ")"

 

[swaink]

Hi Mike

That work across the columns as expected

However the end result is that each column has the same formula =SUM($I$4:$I$249)

My expectation is that each column sum would refer to that specific column I.E
=SUM($I$4:$I$249),=SUM($J$4:$J$249),=SUM($K$4:$K$249) etc

Kev

 

[Mike Blackman]

Sorry,

Range(Range("I4"), Cells(4, Columns.Count).End(xlToLeft)).Formula = "=SUM(" & Range("I4:I" & lastrow).Address(False, False) & ")"

 

[swaink]

Thank you Mike

Thats fantastic it works great

I really appreciate the help this one had me stumped

Kev