Write a dynamic sum formula vba that takes range from another sheet-CodePudding

I am trying to calculate sum in cell "I13" of sheet2 with inputs based on the dynamic range.

Formula

range("I13").formula= "=sum('sheet1'!A1:A3)"

works but the range can be dynamic. For this I have used lr to identify the last row in the range

lr=cells(rows.count,1).end(xlup).row

Now, I want to modify the above formula such that in place of A3, it takes last cell. i.e. A&lr

Have tried using range("I13").formula= "=sum('sheet1'!A1:A"&lr)", but it results in error

Sub MMM()
Windows("Template.xlsx").activate
sheets("sheet1").select
range("a1").select

lr=cells(rows.count,1).end(xlup).row

sheets("sheet2").select

'this code works. But want dynamic range
'range("I13").formula =  "= SUM('sheet1'!A1:A3)"

range("I13").formula =  "= sum('sheet1'!A1:A&lr)"

End Sub

CodePudding user response：

Try to combine the strings for the formula:

range("I13").formula =  "= sum('sheet1'!A1:A" & CStr(lr) & ")"

CodePudding user response：

you can try to define the variable:

Option Explicit ' It should be used when you define variable
   Sub MMM()
   Dim lr as Range ' Define variable
        
     Windows("Template.xlsx").activate
     sheets("sheet1").select
     range("a1").select
        
     lr=cells(rows.count,1).end(xlup).row
        
     sheets("sheet2").select
                       
        range("I13").formula =  "= sum('sheet1'!A1:A&lr)"    
   End Sub