For Next loop - introducing a variable to control my macro

[tbablue]

The rudimentary code below passes a value to the macro PRINTz_ONE, runs PRINTz_ONE then introduces the next value, .......... and so on.
Sub PRINTz_GRP()
On Error Resume Next
With Range("SITEx_PICKER")
.Value = "ROLLED-UP": PRINTz_ONE
.Value = "NYC": PRINTz_ONE
.Value = "MUNICH": PRINTz_ONE
.Value = "WA": PRINTz_ONE
End With
End Sub

This works fine - but it isn't very flexible. I need to specify each site in the code when I want that value passed to PRINTz_ONE - and remove it when I don't.

I need a loop that will reference a lookup table within the workbook ('PPS'!D3:E12), where D contains a list of sites (NYC, PARIS, LONDON, etc) and column E contains the values YES or NO. I need the loop to only run PRINTz_ONE for the sites that have the value YES ascribed.

Can any one help me? Any assistance welcomed.

 

[PatOBrien198]

Maybe something like:

Sub Loop_Print()
Dim ws As Worksheet
Dim rng As Range
Dim r As Integer
Set ws = Sheets("PPS")
Set rng = ws.Range("D3")
r = 0
Do While rng.Offset(r, 0) <> ""
    If UCase(rng.Offset(r, 1)) = "YES" Then
        Printz_One (rng.Offset(r, 0))
    end if
    r = r + 1
Loop
End Sub

Sub Printz_One(SiteName As String)
    'do your printing stuff
End Sub

 

[Fluff]

One option would be something like this

Sub printz_grp()

   Dim Cl As Range
   
   For Each Cl In Sheets("PPS").Range("E3:E12")
      If LCase(Cl.Value) = "yes" Then Call printz_one(Cl.Offset(, -1).Value)
   Next Cl
End Sub

Sub printz_one(MySite As String)

End Sub

Where in the printz_one macro you replace using the range SITEx_PICKER with MySite

 

[tbablue]

Thank you all.

Notably PatOBrien198 & Fluff.

I was able to amend my code to the following:

Sub PRINTz_GRP()

On Error Resume Next

For Each Cl In Sheets("PPS").Range("E3:E12")
If LCase(Cl.Value) = "yes" Then Range("SITEx_PICKER").Value = (Cl.Offset(, -1).Value): PRINTz_ONE
Next Cl

End Sub

This now runs exactly as I need.

 

[Fluff]

Glad we could help & thanks for the feedback