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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Aug 04 '20 at 12:52
Only the steps between `For ....` and `Next` are repeated, not the statements after the `Next`. — Luuk, Aug 04 '20 at 12:56
@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, Aug 04 '20 at 13:20
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, Aug 04 '20 at 13:23
@BigBen you are right. Just checked my code, so how to do Ai kinda thing? — noob, Aug 04 '20 at 13:39

Ron Rosenfeld · Accepted Answer · 2020-08-04T15:20:54.620

1

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

edited Aug 04 '20 at 15:20

answered Aug 04 '20 at 15:14

Ron Rosenfeld

53,870
7
28
60

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

1 Answers1