在 VBA 中使用带计数器的 For 循环

Using a For Loop with a Counter in VBA

提问by William

I am trying to construct a loop that will pull data from two separate sheets and place them into a single table. I was trying to get it to place the data going across the row to the end, then move down one row and repeat until it reaches the end of the table. The Changesand WTDcolumns I and going to figure after the fact. I am just trying to get the This Yearand Last Yeardata dropped in. here is the coding that I have.

我正在尝试构建一个循环，该循环将从两个单独的工作表中提取数据并将它们放入一个表中。我试图让它将跨行的数据放置到末尾，然后向下移动一行并重复直到它到达表格的末尾。在Changes与WTD我和事后将数字列。我只是想获得This Year和Last Year数据滴入。这里是编码，我有。

Sub MakeTable()

Application.ScreenUpdating = False

Dim gRange As Range
Dim i As Integer
Dim j As Integer
Dim baseSheet As Worksheet
Dim from1Sheet As Worksheet
Dim from2Sheet As Worksheet
Dim baseBaseCell As Range
Dim baseFrom1Cell As Range
Dim baseFrom2Cell As Range


Set baseSheet = Sheets("Chart")
Set from1Sheet = Sheets("Week")
Set from2Sheet = Sheets("WeekMinusYear")
Set baseBaseCell = Sheets("Chart").Range("B3")
Set baseFrom1Cell = Sheets("Week").Range("C2")
Set baseFrom2Cell = Sheets("WeekMinusYear").Range("C2")




For i = 0 To 4
    For j = 0 To 12

        If i = ((4 Mod 2) <> 0) Then
            setRawData1
        Else
            setRawData2
        End If

    Next j
Next i


End Sub
Function setRawData1()


Dim baseSheet As Worksheet
Dim from1Sheet As Worksheet
Dim from2Sheet As Worksheet
Dim baseBaseCell As Range
Dim baseFrom1Cell As Range
Dim baseFrom2Cell As Range

Set baseSheet = Sheets("Chart")
Set from1Sheet = Sheets("Week")
Set from2Sheet = Sheets("WeekMinusYear")
Set baseBaseCell = Sheets("Chart").Range("B3")
Set baseFrom1Cell = Sheets("Week").Range("C2")
Set baseFrom2Cell = Sheets("WeekMinusYear").Range("C2")


'Sales
baseBaseCell.Offset(0, 0) = baseFrom1Cell.Value 'This Year

'Cost
baseBaseCell.Offset(0, 3) = baseFrom1Cell.Offset(0, 1) 'This Year

'Margin
baseBaseCell.Offset(0, 6) = baseFrom1Cell.Offset(0, 2) 'This Year

'Basis Points
baseBaseCell.Offset(0, 9) = baseFrom1Cell.Offset(0, 3) 'This Year



End Function
Function setRawData2()

Dim baseSheet As Worksheet
Dim from1Sheet As Worksheet
Dim from2Sheet As Worksheet
Dim baseBaseCell As Range
Dim baseFrom1Cell As Range
Dim baseFrom2Cell As Range

Set baseSheet = Sheets("Chart")
Set from1Sheet = Sheets("Week")
Set from2Sheet = Sheets("WeekMinusYear")
Set baseBaseCell = Sheets("Chart").Range("B3")
Set baseFrom1Cell = Sheets("Week").Range("C2")
Set baseFrom2Cell = Sheets("WeekMinusYear").Range("C2")

'Sales
baseBaseCell.Offset(0, 1) = baseFrom2Cell.Value 'Last Year

'Cost
baseBaseCell.Offset(0, 4) = baseFrom2Cell.Offset(0, 1) 'Last Year

'Margin
baseBaseCell.Offset(0, 7) = baseFrom2Cell.Offset(0, 2) 'Last Year

'Basis Points
baseBaseCell.Offset(0, 10) = baseFrom2Cell.Offset(0, 3) 'Last Year

End Function

Here is an example of the table I have.

这是我拥有的表格的示例。

Sorry for the poor quality on the image. All the columns are broken up like they should be. I have tried several different methods for accomplishing this. as it is now it will go through and drop the last year information into the correct columns, but for the first row only. Any help I could get would be greatly appreciated!

抱歉图片质量差。所有的列都像它们应该的那样被分解。我已经尝试了几种不同的方法来实现这一点。就像现在一样，它将遍历并将去年的信息放入正确的列中，但仅限于第一行。我能得到的任何帮助将不胜感激！

回答by enderland

Use Option Explicit... otherwise you can run into weird problems with using so similar variable names. But this isn't your issue.

使用Option Explicit... 否则，使用如此相似的变量名可能会遇到奇怪的问题。但这不是你的问题。

Each of your methods does not actually change the cell it is writing to based on row.

您的每个方法实际上都不会根据行更改它正在写入的单元格。

For example (removing additional code):

例如（删除附加代码）：

Function setRawData1()

Set baseFrom1Cell = Sheets("Week").Range("C2")
baseBaseCell.Offset(0, 0) = baseFrom1Cell.Value 'This Year

'Cost
baseBaseCell.Offset(0, 3) = baseFrom1Cell.Offset(0, 1) 'This Year

'Margin
baseBaseCell.Offset(0, 6) = baseFrom1Cell.Offset(0, 2) 'This Year

'Basis Points
baseBaseCell.Offset(0, 9) = baseFrom1Cell.Offset(0, 3) 'This Year

End Function

This function will ALWAYS be setting the exact same cells (regardless as to what your loop status is) because nothing in there ever causes them to be offset in rows.

此函数将始终设置完全相同的单元格（无论您的循环状态如何），因为其中没有任何内容会导致它们在行中偏移。

So when you call

所以当你打电话

 If i = ((4 Mod 2) <> 0) Then
        setRawData1    'write to same row every time...
    Else
        setRawData2     'write to same row every time...
    End If

I would suggest modifying your functions to be something like:

我建议将您的功能修改为：

Function setRawData1(int rowOffset)    
    baseBaseCell.Offset(rowOffset, 0) = baseFrom1Cell.Value 'This Year

    'Cost
     baseBaseCell.Offset(rowOffset, 3) = baseFrom1Cell.Offset(0, 1) 'This Year

    'Margin
    baseBaseCell.Offset(rowOffset, 6) = baseFrom1Cell.Offset(0, 2) 'This Year

    'Basis Points
     baseBaseCell.Offset(rowOffset, 9) = baseFrom1Cell.Offset(0, 3) 'This Year

and then passing the correct offset values to each of them

然后将正确的偏移值传递给每个人