excel - Vba for loop to repeat code output for cell rage

Question

How can I make a for loop to repeat this code between N5 to N11

Dim rw As Long
    
    If Range("K6") = True Then 'Holiday Check
           
        rw = [RandBetween(6,25)]
        Range("N5") = Cells(rw, 21)
               
    ElseIf Range("I6") = True Then 'Weekend Check
    
       
        rw = [RandBetween(5,28)]
        Range("N5") = Cells(rw, 20)
        
    Else 'Else Weekday
        
        rw = [RandBetween(5,65)]
        Range("N5") = Cells(rw, 19)
   
 
               
    End If

Thank you in advance :)

标签： excelvbafor-loop

Answer 1

You can see the documentation for For...Next and For Each...Nextloops which explains how the syntax works for each of the loops.

To achieve your goal, you can consider the following:

• Use a variable for either your cell reference or row number
• Increment that variable with each step of your loop
• Replace the hard coded range reference to reference either the cell or row number variable.

I will demonstrate the For Each...Next method.

Something like this:

Private Sub UsingAForLoop()

Dim TargetCell As Range

For Each TargetCell In Range("N5:N11")

    If Range("K6") = True Then 'Holiday Check
        rw = [RandBetween(6,25)]
        TargetCell = Cells(rw, 21)    
    ElseIf Range("I6") = True Then 'Weekend Check 
        rw = [RandBetween(5,28)]
        TargetCell = Cells(rw, 20)
    Else 'Else Weekday  
        rw = [RandBetween(5,65)]
        TargetCell = Cells(rw, 19)
    End If

Next TargetCell

End Sub

This example loops through each cell in the range Range("N5:N11") and follows your if logic each time. When the condition is met, the TargetCell for that iteration will be populated with the data per your Cells() reference.

If you need to change the other references as part of your IF...Then statements you can follow the same logic.