Loop through list and run code for every item (VBA)

Question

I have a list of names, and some code that I would like to run for every single name. What I'm starting with is this:

Dim cell As Range
For Each cell In Worksheets("Reference").Range("b2:b237")
[rest of my code here]
Next cell

The issue is, what I'm actually trying to do is:

Step 1) Select a name from a drop down list in cell A1

Step 2) There are a bunch of other cells with formulas that reference A1

Step 3) Run code

Step 4) Select next name from drop down list in A1, repeat Steps 2 & 3, until end of list.

Edit: I found something on an old thread that seems to work for what I'm doing:

Sub Macro1()
    Sheets("Sheet2").Activate
    Range("A1").Select

Do While True
    If Selection.Value = "" Then
        Exit Do
    Else
        Selection.Copy
        Sheets("Sheet1").Activate
        Range("A1").Activate
        ActiveSheet.Paste

        [rest of my code]

        Sheets("Sheet2").Activate
        Selection.Offset(1, 0).Select

    End If
Loop
End Sub

This should do the job, but if anyone has a more efficient way rather than copying and pasting each value from the list to the cell, that would be very helpful too!

Thank you.

SO is not a free code writing platform, please show some of the attempts you've made to solve your problem. Also try to specify what exactly it is you're trying to achieve, I'm having a hard time understanding what your code is supposed to do. — riskypenguin, Sep 25 '19 at 10:20
@Dorian it is not, it is just 200 rows of names in a column on another worksheet — looopa, Sep 25 '19 at 10:45
@M.Schalk Thanks for the suggestions, I tried a few things but I'm quite a beginner so I figured they're probably wrong anyway and the easiest way to ask the question was to keep it open ended.. I'm working on improving the qn — looopa, Sep 25 '19 at 10:48
@HarassedDad from a column of values in another sheet. This solution doesnt necessarily have to involve a drop down list — looopa, Sep 25 '19 at 11:08
To start to improve your code, read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Ron Rosenfeld, Sep 25 '19 at 12:06

score 0 · Answer 1 · answered Sep 25 '19 at 13:22

This will take each name in a range and put it into a cell sequentially - you will need to edit to put your sheetnames and ranges in

Sub LoopThroughNames()
dim RangeWithNames as range
'define list of names - needs editing
set RangeWithNames = Worksheets("othersheetname").Range("range with names")
dim TargetCell as range
set TargetCell = worksheets("Sheet with calcs").Range("A1") 'top sheet, cell A1 edit as needed
dim r as range
for each r in RangeWithNames
      targetcell= r  'assign name into A1
'do your stuff
next r
End Sub

Loop through list and run code for every item (VBA)

1 Answers1