能否更新VBA For循环的循环条件？场景问题求解

Great question—yes, this problem absolutely can be solved! The issue with your original For loop is that VBA evaluates the loop's start, end, and step values only once when the loop first runs. Changing lr or i inside the loop doesn't alter the loop's execution because those parameters are fixed at initialization. That's why your loop ends at i=51 instead of continuing to 500.

The best way to handle dynamic loop bounds (like switching between worksheets mid-loop) is to use a Do While loop instead. Unlike For loops, Do While rechecks its condition on every iteration, so you can update your loop variables dynamically as needed.

Example for Your Worksheet Scenario #

Let's say you want to loop through rows in Sheet1 until you hit a specific condition, then switch to Sheet2 and loop through all its rows. Here's how you'd implement that:

Sub CrossWorksheetLoop()
    Dim activeWs As Worksheet
    Dim currentRow As Long
    Dim lastRow As Long
    
    ' Initialize with your first worksheet
    Set activeWs = ThisWorkbook.Worksheets("Sheet1")
    currentRow = 1
    lastRow = activeWs.Cells(activeWs.Rows.Count, "A").End(xlUp).Row ' Get last row of Sheet1
    
    Do While currentRow <= lastRow
        ' Check your condition to switch worksheets
        If activeWs.Cells(currentRow, "B").Value = "Switch Now" Then ' Adjust condition to your needs
            ' Switch to the second worksheet and update loop bounds
            Set activeWs = ThisWorkbook.Worksheets("Sheet2")
            lastRow = activeWs.Cells(activeWs.Rows.Count, "A").End(xlUp).Row
            ' Optional: Reset currentRow to 1 if you want to start at the top of Sheet2
            ' currentRow = 1
        End If
        
        ' Add your row processing logic here
        Debug.Print "Processing " & activeWs.Name & " Row " & currentRow
        
        currentRow = currentRow + 1
    Loop
End Sub

How This Works #

1. We start with Sheet1 and set our initial loop bounds.
2. On each iteration, we check if we need to switch worksheets. When the condition is met, we update activeWs to point to Sheet2 and reset lastRow to the last row of Sheet2.
3. The Do While loop re-evaluates currentRow <= lastRow every time, so it automatically adapts to the new worksheet's bounds.

Alternative: Nested For Loops #

If you prefer to stick with For loops, you can exit the first loop when your condition is met, then start a new For loop for the second worksheet:

Sub NestedForLoops()
    Dim lr1 As Long, lr2 As Long
    Dim i As Long
    
    lr1 = ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
    
    For i = 1 To lr1
        If ThisWorkbook.Worksheets("Sheet1").Cells(i, "B").Value = "Switch Now" Then
            ' Exit the first loop and start the second
            lr2 = ThisWorkbook.Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
            For i = 1 To lr2
                ' Process Sheet2 rows here
                Debug.Print "Processing Sheet2 Row " & i
            Next i
            Exit For ' Exit the first loop after switching
        End If
        ' Process Sheet1 rows here
        Debug.Print "Processing Sheet1 Row " & i
    Next i
End Sub

This works, but the Do While approach is more flexible if you might need to switch back and forth between worksheets multiple times.

内容的提问来源于stack exchange，提问作者Andreas