在vba循环中使用单元格有问题-HavingissuesusingCellsformyrangeinmyvbaloop

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?

我正在尝试创建一个循环，它从一个电子表格中获取多个范围的和，并在另一个电子表格中粘贴值。例如，当我使用range(“E10:E54”)时，我的循环将会工作，但是它会在我所有的单元格中粘贴相同的和值。我正在尝试使用Range(Cells())来移动到下一个范围并得到我的和，但是当我尝试时，我得到了一个错误。我的代码如下。我缺少什么?

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.

这是我当前的代码。我只替换了Range(cell (10,5)， Cells(545,5))而不是Range(e10:E54)，我得到了一个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

1 个解决方案

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