CODEWITHSUNDEEP
excelvba
pranav
pranav

Reputation: 1111

VBA coding using last used column number

I'm trying to write VBA code to sum up cells, in the process i calculate the latest updated column using the statement: 

clncnt1 = wb.Worksheets("Sheet1").Range("XFD16").End(-4159).Column

Suppose I capture the clncnt1 as 20 which is column P. Now how do I use: 

wb.Worksheets("sheet1").Range("A1").Offset(16, clncnt).Formula = "=sum(*P12:P13*)"

using the column number clncnt1 that I just captured?

Upvotes: 1

Views: 56

Answers (2)

pranav
pranav

Reputation: 1111

this worked wb.Worksheets("sheet1").Range("A1").Offset(11, clncnt + 3).Formula = "=sum(" & _ wb.Worksheets("sheet1").Range("A1").Offset(8, clncnt + 3).Address & ":" & _ wb.Worksheets("sheet1").Range("A1").Offset(9, clncnt + 3).Address & ")"

Upvotes: 0

99moorem
99moorem

Reputation: 1983

replace your sum with below

"=sum(" & Cells(12, clncnt1 ).Address & ":" & Cells(13, clncnt1 ).Address & ")"

you are actually getting the addresses of the range and then concatenating it all together. Full code:

clncnt1 = Worksheets("sheet1").Range("XFD16").End(-4159).Column
Worksheets("sheet1").Range("A1").Offset(16, clncnt1).Formula = "=sum(" & Cells(12, clncnt1).Address & ":" & Cells(13, clncnt1).Address & ")"

Upvotes: 1

Related Questions