Excel VBA for loop: Take values from a list and execute row wise

Question

I have an excel macro as below

sub macro1()
Range("A1").Select #A1 cell will be selected and analysis done on that 
Selection.Copy
do Action 1 on Ai cell where i is from 1 to 4
end sub

sub macro2()
do Action 2 on Ai cell where i is from 1 to 4
end sub

sub macro3()
do Action 3 on Ai cell where i is from 1 to 4
end sub

I want that all actions 1 , 2 and 3 are done from A1 to A4 sequentially. First do all steps for A1, then do all steps for A2, then do all steps for A3 and finally do everything for A4.

I want to do something like Ai Sub macro1() Dim i As Integer For i = 1 To 4 Range("Ai").Select #this syntax is wrong, but how to do Ai kind of range, so that it takes A1, does all 3actions, then A2 and so on till A4 cell Selection.Copy Next i Do Action 1 on Ai cell where i is from 1 to 4 End Sub

Sub macro2()
Do Action 2 on Ai cell where i is from 1 to 4
End Sub

Sub macro3()
Do Action 3 on Ai cell where i is from 1 to 4
End Sub

Despite having a for loop, my macro only runs for A1 cell , it does not run for A2, then A3 then A4. Please tell what could be wrong

There's no way Range("Ai").Select would work. Variables don't belong inside quotes: Range("A" & i).Select. But you shouldn't select anyway, see this. — BigBen
Only the steps between For .... and Next are repeated, not the statements after the Next. — Luuk
@BigBen: I am not just selecting A1, I am also copying A1 and then pasting it somewhere (pasting it is say action1 here)...so how to proceeed — noob
I'm saying that there's your claim that "my macro only runs for A1 cell" is not possible at all given the provided code snippet. You could possibly call the other subroutines within the loop and pass them a range as a parameter. — BigBen

Ron Rosenfeld Ron Rosenfeld · Accepted Answer · 2020-08-04T15:14:44

Hard to know without more information, but maybe something like:

Sub macro2()
 Dim myRng As Range, myCell As Range
 
 Set myRng = Range("A1:A4")
 For Each myCell In myRng
    Action1 myCell
    Action2 myCell
    Action3 myCell
Next myCell
End Sub

Sub Action1(rg As Range)
 'do Action 1
End Sub

Sub Action2(rg As Range)
 ' do Action 2
End Sub

Sub Action3(rg As Range)
 ' do Action 3
End Sub

Excel VBA for loop: Take values from a list and execute row wise

1 Answers