excel - 多次重新打印行并进行修改

Question

我提前为我的初学者道歉，但我有几行我想在文档的另一个位置重印 12 次，总和除以 12。

我要这个：

看起来像这样：

Sub computeThis()
Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("A2:D3")

For Each row In rng.Rows 'Throws no error but doesn't seem to loop twice either'
    
    Dim i As Integer
    
    'Set the starting cell number'
    Dim x As Integer
    x = 2
    
    'Repeat 12 times..'
    For i = 1 To 12
        '..with new values'
        Cells(x, 6).Value = Range("A2").Value 'Needs to update with each loop'
        Cells(x, 7).Value = i 'Works OK'
        Cells(x, 8).Value = Range("C2").Value 'Needs to update with each loop'
        Cells(x, 9).Value = Range("D2").Value / 12 'Needs to update with each loop'
        
        x = x + 1
    Next i
Next row
End Sub

问题如下：

• 它只运行了 12 次，所以似乎只有 For-loop 运行
• A-column，即Account-column，需要根据下一行的内容动态变化
• C列，即Kst列，也需要动态变化
• Sum 列也是如此，因为那里的值也会发生变化

我意识到这个问题有点愚蠢，但我喜欢这些问题的一些指示，因为我并没有真正在这里前进。

谢谢！

标签： excelvba

Answer 1

你X在外循环开始时被重置为 2，所以看起来它运行了一次，但它实际上覆盖了你的第一个循环。

我添加了一个新变量来增加行号。我还将您的类型从 Integer 更改为 Long，不要在 VBA 中使用 Integer 类型，这会导致溢出错误。

Sub computeThis()
Dim rng As Range
Dim row As Range
Dim cell As Range

Set rng = Range("A2:D3")
Dim x As Long
x = 2
Dim j As Long
j = 2
For Each row In rng.Rows 'Throws no error but doesn't seem to loop twice either'
    
    Dim i As Long

    
    'Repeat 12 times..'
    For i = 1 To 12
        '..with new values'
        Cells(j, 6).Value = Range("A" & x).Value 'Needs to update with each loop'
        Cells(j, 7).Value = i 'Works OK'
        Cells(j, 8).Value = Range("C" & x).Value 'Needs to update with each loop'
        Cells(j, 9).Value = Range("D" & x).Value / 12 'Needs to update with each loop'
        j = j + 1

    Next i
    x = x + 1
Next row
End Sub