How can i erease the second double for loop and shorten my code?-CodePudding

Sub Makro1()

For i = 3 To 8        'row 3 to row 8
For j = 34 To 36      'column 34 to 36
    Cells(j, 7   i) = Cells(j, i)
Next j
Next i

For i = 3 To 8
For j = 38 To 40
    Cells(j, 7   i) = Cells(j, i)
Next j
Next i

End Sub

The logic in both double for loops is the same. i am copying every cell from row 34 to 36 and column 3 to column 8. I place this block of cells one column next to it. So it looks like this:

Now i want to repeat this process many times, because there are many other data blocks like this in my sheet. the next block starts at row 38 to 40, the next one 42 to 44 and so on. So there is always one row distance between the blocks. How can i loop though this, anybody got an idea?

CodePudding user response：

I think your column/row comments in your question might be the wrong way around, but I have tried to write something that should do what I think you want:

Sub test()
    
    Dim ofst As Long, iterations As Long, stepchange As Long
    
    iterations = 3 ' loop three times - change as required
    stepchange = 4 ' perform the copy every 4 rows
    
    With ActiveSheet
    
        For ofst = 1 To iterations * stepchange Step stepchange
        
            With .Range(.Cells(34   ofst - 1, 3), .Cells(36   ofst - 1, 8))
                .Offset(, 7).Value = .Value
            End With
            
        Next
        
    End With
    
End Sub