Loop column values and run procedures for each value

[GDunn]

Hi,


I am trying to:

• Loop through the list of names in 'Set RmRng = Worksheets("Data").Range("B9:B25")'
• Copy and Paste each value into 'Set TgRng = Worksheets("Data").Range("C1")'
• Run procedures
• Repeat for next value in range 'Set RmRng = Worksheets("Data").Range("B9:B25") until last value in range

But am unable to loop once I leave the range. Does anyone know if this can be done?

Sub process_test()
Dim RmRng As Range
Dim TgRng As Range
Set RmRng = Worksheets("Data").Range("B9:B25")
Set TgRng = Worksheets("Data").Range("C1")

'unhide sheets
UnhideAllSheets

'select CAM/BDM name
Worksheets("Data").Select
RmRng.Select
ActiveCell.Offset(0, 0).Copy
TgRng.PasteSpecial (xlPasteAllUsingSourceTheme)

'refresh pivot tables name range
Refresh_Pivots

'hide Team sheets
Hide_Sheet

'hide Data sheet
Hide_Data

'print PDF sheets to file
PrintPDF

 

[pboltonchina]

Maybe you could adapt this to suit your needs

Sub cyclethroughlist()
Dim LR As Long, i As Long
With Sheets("Sheet2")
    LR = .Range("A" & Rows.count).End(xlUp).Row
    For i = 1 To LR
        .Range("A" & i).Copy Destination:=Sheets("Sheet1").Range("A3")
        Call Macro2
    Next i
End With
End Sub

 

[cjo]

Hi,
take a look at this code

Sub process_test()
    Dim RmRng                      As Range
    Dim TgRng                      As Range
    Dim i                          As Long

    Set RmRng = Worksheets("Data").Range("B9:B25")
    Set TgRng = Worksheets("Data").Range("C1")

    'unhide sheets
    UnhideAllSheets


    'NewComment.  There is rarely a need to select something you're working with.
    'select CAM/BDM name
    '    Worksheets("Data").Select
    '    RmRng.Select
    'NewComment.  activecell.Offset(0,0) is the same thing as activecell,
    'NewComment.  which is the same thing as RmRng (which I'm assuming is the
    'newcomment.  range you want to loop through)
    '    ActiveCell.Offset(0, 0).Copy

    'NewComment. using a long counter variable to keep track of what you're looping through
    For i = 9 To 25
        'NewComment. I'm assuming that cell C1 doesn't need all the formatting and whatnot
        'NewComment. to be brought over, just the value.
        
        TgRng.Value = Worksheets("Data").Range("B" & i).Value

        'NewComment. If you do need all the formatting and whatnot, then use these lines
        'Worksheets("Data").Range("B" & i).copy
        'tgrng.PasteSpecial(xlPasteAllUsingSourceTheme)

        'NewComment.  Assuming that each of these subs need to be ran for each cell in the
        'NewComment. range b9:b25. If not, then place the subs that don't need to be
        'NewComment. ran on every iteration AFTER the next i line.
        
        'refresh pivot tables name range
        Refresh_Pivots

        'hide Team sheets
        Hide_Sheet

        'hide Data sheet
        Hide_Data

        'print PDF sheets to file
        PrintPDF

    Next i
End Sub

...and for possibly more info than you looking for, you can loop through a range more directly by looping through it's collection of range. For example,
instead of this

Sub LoopStyle1()
    Dim i As Long
    For i = 1 To 5
        MsgBox Range("A" & i).Value
    Next i
End Sub

you could use this

Sub LoopStyle2()
    Dim cl As Range
    For Each cl In Range("A1:A5")
        MsgBox cl.Value
    Next cl
End Sub

 

[GDunn]

Thanks pboltonchina and cjo

Both codes are very helpful.

cjo thanks for the additional loop examples.