Having issues using Cells for my range in my vba loop

Question

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

Scott Craner Scott Craner · Accepted Answer · 2016-05-23T13:34:01

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.

Having issues using Cells for my range in my vba loop

1 Answers