excelvba

Reprint rows multiple times with modifications

I want to reprint rows 12 times on another location in the document with the sum divided by 12.

I have this:
enter image description here

I want this:
enter image description here

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

Problems:

Solution

  • Your X is getting reset to 2 at the beginning of the outer loop, so it looks like it is running once but it's actually overwriting your first loop.

    I added a new variable to increment the row number. I also changed your types from Integer to Long, don't use Integer type in VBA it can cause overflow errors.

    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