Reputation: 45
I'm trying to make a loop that gets the sum of multiple ranges from one spreadsheet and pastes the value in another one. When I use range("E10:E54") for example, my loop will work but then it will paste the same sum value in all of my cells. I am trying to use Range(Cells()) to move to the next range and get my sum but I get a bug when I try that. My code is below. What am I missing?
This is my current code. I've only substituted Range(Cells(10, 5), Cells(54, 5))) instead of Range("e10:E54") and I get a bug.
Dim e As Long
e = 1
Do While e < 39
Cells(e, 5).Value = Application.Sum(Workbooks("Current Fcst.xlsm").Sheets("Sales").Range(Cells(10, 5), Cells(54, 5)))
e = e + 3
Loop
End Sub
Upvotes: 1
Views: 67
Reputation: 152660
You get the bug because the Cells()
inside the range are looking at the active sheet and the Range is looking at a different sheet. This is a good time to use a With Block.
Dim e As Long
Dim ws as WorkSheet
Set ws = ActiveSheet
e = 1
Do While e < 39
With Workbooks("Current Fcst.xlsm").Sheets("Sales")
ws.Cells(e, 5).Value = Application.Sum(.Range(.Cells(10, 5), .Cells(54, 5)))
End With
e = e + 3
Loop
By setting the parent sheets directly including the active sheet, excel knows to which sheet each range object belongs.
The .
in front of the three object tell excel it belongs to the With block parent.
Upvotes: 1