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

Question

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

If this is VBA, please show us your code. Snippets do not help as it does not give the total overview.. — Aldert, Aug 05 '22 at 05:55
Please do not post pictures of code. Instead, post the code itself (edit your question) so people can copy/paste it if they want to. — braX, Aug 05 '22 at 06:07
Please don't use pictures of code - just copy and paste it into your post as text. — Tim Williams, Aug 05 '22 at 06:07

score 0 · Answer 1 · answered Aug 05 '22 at 07:09

0

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

answered Aug 05 '22 at 07:09

Reni

3
3

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 05 '22 at 12:23
This won't solve the problem as the formula is still wrong. – Ike Aug 05 '22 at 12:36

score 0 · Answer 2 · answered Aug 05 '22 at 12:36

You have to join the string for the formula like this:

"=SUM('Sheet1'!A1:A" & lastRow & ")"

Alternatively: If you set the whole range to be summed then you can use the Address of this range. The External-parameter returns the sheet name as well.

Sub MMM()
Dim wb As Workbook: Set wb = ThisWorkbook

Dim wsSource As Worksheet: Set wsSource = wb.Worksheets("Sheet1")
Dim wsTarget As Worksheet: Set wsTarget = wb.Worksheets("Sheet2")

Dim rgDataToSum As Range
With wsSource
    Set rgDataToSum = .Range("A1", .Cells(.Rows.Count, 1).End(xlUp))
End With

wsTarget.Range("I13").Formula = "=SUM(" & rgDataToSum.Address(True, True, External:=True) & ")"

End Sub

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

2 Answers2