如何高效控制VBA多层嵌套循环避免运行时错误？代码求助

Hey there, let's get your code working properly. First, let's tackle that error you're seeing:

The Root Cause of "For Control is already in use" #

You're reusing the same loop control variable c in both your outer and inner For Each loops. VBA doesn't allow this—once you start a loop with For Each c In ..., you can't start another loop with the same c variable until the first loop finishes. That's exactly what's triggering the error.

Corrected Full Code #

Here's a revised version of your code that fixes the error, cleans up the logic, and makes it more efficient:

Option Explicit ' Always add this to force variable declaration (catches typos!)

Private Sub CommandButton1_Click()
    Dim SAPWS As Worksheet
    Dim SFWS As Worksheet
    Dim SAPProjectDesc As Range
    Dim SFProjectDesc As Range
    Dim SAPSFBuyPrice As Range
    Dim SAPSFSellPrice As Range
    Dim SFBuyPrice As Range
    Dim SFSellPrice As Range
    Dim SFMaterialCode As Range
    Dim SAPMaterialCode As Range
    Dim i As Long
    Dim c As Range ' Outer loop variable for Project Description
    Dim d As Range ' Inner loop variable for Material Code
    Dim matchFound As Boolean
    Dim projectMatchFound As Boolean
    
    ' Set worksheet references explicitly (better than using UsedRange directly)
    Set SAPWS = ThisWorkbook.Worksheets("SAP")
    Set SFWS = ThisWorkbook.Worksheets("SF")
    
    ' Define column ranges (adjust row starts if your headers are on row 1)
    Set SAPProjectDesc = SAPWS.Range("E2:E" & SAPWS.Cells(SAPWS.Rows.Count, "E").End(xlUp).Row)
    Set SFProjectDesc = SFWS.Range("D2:D" & SFWS.Cells(SFWS.Rows.Count, "D").End(xlUp).Row)
    Set SAPSFBuyPrice = SAPWS.Range("P2:P" & SAPWS.Cells(SAPWS.Rows.Count, "P").End(xlUp).Row)
    Set SAPSFSellPrice = SAPWS.Range("Q2:Q" & SAPWS.Cells(SAPWS.Rows.Count, "Q").End(xlUp).Row)
    Set SFBuyPrice = SFWS.Range("AA2:AA" & SFWS.Cells(SFWS.Rows.Count, "AA").End(xlUp).Row)
    Set SFSellPrice = SFWS.Range("Y2:Y" & SFWS.Cells(SFWS.Rows.Count, "Y").End(xlUp).Row)
    Set SFMaterialCode = SFWS.Range("W2:W" & SFWS.Cells(SFWS.Rows.Count, "W").End(xlUp).Row)
    Set SAPMaterialCode = SAPWS.Range("N2:N" & SAPWS.Cells(SAPWS.Rows.Count, "N").End(xlUp).Row)
    
    matchFound = False
    projectMatchFound = False
    
    ' Loop through each row in SAP's Project Description column
    For i = 1 To SAPProjectDesc.Rows.Count
        projectMatchFound = False
        ' Look for matching Project Description in SF
        For Each c In SFProjectDesc.Cells
            If c.Value2 = SAPProjectDesc.Cells(i).Value2 Then
                projectMatchFound = True
                ' Now look for matching Material Code in the same project's row
                Set d = SFMaterialCode.Cells(c.Row - SFProjectDesc.Row + 1)
                If d.Value2 = SAPMaterialCode.Cells(i).Value2 Then
                    matchFound = True
                    ' Sync prices directly (no need to activate sheets!)
                    SAPSFSellPrice.Cells(i).Value2 = SFSellPrice.Cells(c.Row - SFProjectDesc.Row + 1).Value2
                    SAPSFBuyPrice.Cells(i).Value2 = SFBuyPrice.Cells(c.Row - SFProjectDesc.Row + 1).Value2
                    Exit For ' Exit project loop once full match is found
                Else
                    Debug.Print "Project match found for SAP row " & i & ", but no matching material code in SF row " & c.Row
                End If
            End If
        Next c
        
        If Not projectMatchFound Then
            Debug.Print "No project match found for SAP row " & i
        End If
        matchFound = False ' Reset for next SAP row
    Next i
    
    ' Optional: Show a final summary message
    MsgBox "Price sync process completed. Check Immediate Window (Ctrl+G in VBA Editor) for mismatch details.", vbInformation
End Sub

Key Improvements & Fixes #

• Fixed the loop variable conflict: Changed the inner material code check to use a targeted range instead of reusing c, eliminating the control variable error
• Removed unnecessary .Activate calls: Directly assigning values to Range objects is faster and avoids runtime errors caused by sheet activation issues
• Added Option Explicit: Forces you to declare all variables, which catches typos and undefined variable errors early
• More precise range definitions: Instead of using UsedRange (which can include blank rows/columns), we define ranges based on the last used row in each column
• Targeted material code check: When a project matches, we only check the material code in the same SF row (your original code was checking all material codes, not just the one linked to the matched project)
• Non-intrusive feedback: Used Debug.Print instead of repeated MsgBox calls to log mismatches—you can view these in the VBA Immediate Window (Ctrl+G) instead of getting interrupted every time
• Exit loops early: Once a full match is found, we exit the project loop to avoid unnecessary processing

How It Works #

1. We loop through each row in the SAP table's Project Description column
2. For each SAP row, we look for a matching Project Description in the SF table
3. If a project match is found, we check if the Material Code in that same SF row matches the SAP row's Material Code
4. If both match, we sync the Sell Price and Buy Price from SF to SAP
5. All mismatches are logged to the Immediate Window for review

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