CODEWITHSUNDEEP
vbaexcel
Kstar
Kstar

Reputation: 1

Need a loop that re exectutes the same formula but for the following column

as you can see I want to repeat it each time going to the next value... is there any easier way to do this?

I have tried the loop function but I don't know how to get this to work so that it executes to the next column each time for the same row. 

Sub rebuild()
If D28 > 2600000 Then
Range("E$110:I$120").Select
Selection.Copy

Range("E18:Il28").Select
ActiveSheet.Paste
End If

If E28 > 2600000 Then
Range("E$110:I$120").Select
Selection.Copy

Range("F18:Jl28").Select
ActiveSheet.Paste

End If

If F28 > 2600000 Then
Range("E$110:I$120").Select
Selection.Copy

Range("G18:Kl28").Select
ActiveSheet.Paste

End If


If G28 > 2600000 Then
Range("E$110:I$120").Select
Selection.Copy

Range("H18:Ll28").Select
ActiveSheet.Paste

End If


End sub

Upvotes: 0

Views: 31

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

It is really very simple. I have commented the code.

Also you do not need to select a range to copy/paste. You may want to see THIS

Sub rebuild()
    With Sheet1 '~~> Change this to the relevant sheet
        For i = 4 To 6 '<~~ Col 4 (D) to Col 6 (F)
            If .Cells(28, i).Value > 2600000 Then
                '~~> Increment the range wgere you want to paste
                .Range("E$110:I$120").Copy .Range(.Cells(18, i + 1), .Cells(128, i + 5))
            End If
        Next i
    End With
End Sub

Upvotes: 1

Zwo
Zwo

Reputation: 1113

A solution could be to put your variables into an array, and then make a simple for loop.

Dim MyArray(4, 2) as Variant
MyArray(0,0) = D28
MyArray(0,1) = Range("E18:Il28")
MyArray(1,0) = E28
MyArray(1,1) = Range("F18:Jl28")
MyArray(2,0) = F28
MyArray(2,1) = Range("G18:Kl28")
MyArray(3,0) = G28
MyArray(3,1) = Range("H18:Ll28")

For i = LBound(MyArray) to UBound(MyArray)
    If MyArray(i,0) > 2600000 then
        Range("E$110:I$120").Copy
        MyArray(i,1).Paste
    End If
Next

Upvotes: 0

Related Questions