loop vba code to call macro through range

Question

I have a macro which looks at a selection of data in specified cells, it takes the value from each row in the selection and populates an excel spreadsheet. The it calls another macro which runs.

This code works well when I start the start and finish cells, however I want to be able to use a variable range so it automatically finds the last cell and runs through the selection.

I've tried using 'For Each rngMyCell In Sheets("Selection").Range("A43").End(xlDown).Row however this only selects the last item in my list and doesn't run through all of them. Can anyone advise what I'm doing wrong.

Thanks,

    Sub Macro1()

Sheets("Selection").Range("B6").Value = Now()
 Dim rngMyCell As Range
 
 Application.ScreenUpdating = False
 
 For Each rngMyCell In Sheets("Selection").Range("A43:A50")
 Sheets("Selection").Range("C3").Value = rngMyCell
 Call RunAll
 Next rngMyCell
 
 Application.ScreenUpdating = True
Sheets("Selection").Range("B7").Value = Now()
MsgBox ("All done")

End Sub

Rafał Barbacki Rafał Barbacki · Accepted Answer · 2021-06-10T10:44:46

In posted code: For Each rngMyCell In Sheets("Selection").Range("A43").End(xlDown).Row you are looping through a number, but not range (which is wrong). The group in For Each...Next statement should be name of an object collection or array.

To loop dynamically through whole range, try this:

    For Each rngMyCell In Sheets("Selection").Range("A43:A" & Sheets("Selection").Range("A43").End(xlDown).Row)

loop vba code to call macro through range

1 Answers