运行VBA代码遇Do While调试错误，需实现数据同步与公式自动填充

Let’s walk through resolving that Do While loop error and getting your PO addition/replacement code working smoothly. Common issues with such loops include infinite loops from unupdated counters, incorrect range references, or failing to exit the loop when there’s no more data to process. Below is a revised, fully commented version of your code that addresses these problems and meets all your requirements:

Option Explicit ' Always use this to catch undeclared variables and typos

Sub AddNewPO()
    Dim wsNewPOs As Worksheet
    Dim wsSummary As Worksheet
    Dim lastRowNewPOs As Long
    Dim lastRowSummary As Long
    Dim currentRow As Long
    Dim poValue As Variant
    Dim matchRow As Variant
    
    ' Turn off screen updates and events to speed up execution and prevent interruptions
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    ' Set worksheet references (use descriptive names instead of default SheetX to avoid confusion)
    Set wsNewPOs = ThisWorkbook.Sheets("New_POs")
    Set wsSummary = ThisWorkbook.Sheets("Summary by PO")
    
    ' Get last row with data in Column A of both sheets
    lastRowNewPOs = wsNewPOs.Cells(wsNewPOs.Rows.Count, "A").End(xlUp).Row
    currentRow = 2 ' Assuming your data starts at row 2 (skip header row)
    
    ' Do While loop to iterate through each PO in New_POs
    Do While currentRow <= lastRowNewPOs
        poValue = wsNewPOs.Cells(currentRow, "A").Value
        
        ' Skip empty cells in New_POs Column A to avoid unnecessary processing
        If Not IsEmpty(poValue) Then
            ' Check if the PO already exists in Summary sheet Column A
            matchRow = Application.Match(poValue, wsSummary.Columns("A"), 0)
            
            If IsError(matchRow) Then
                ' PO doesn't exist: insert a new row at the end of Summary data
                lastRowSummary = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row
                wsSummary.Rows(lastRowSummary + 1).Insert Shift:=xlDown
                
                ' Add the PO to the newly inserted row
                wsSummary.Cells(lastRowSummary + 1, "A").Value = poValue
            Else
                ' PO exists: replace the value (add code here to update other columns if needed)
                wsSummary.Cells(matchRow, "A").Value = poValue
                ' Example: wsSummary.Cells(matchRow, "B").Value = wsNewPOs.Cells(currentRow, "B").Value
            End If
        End If
        
        ' Critical: increment the counter to avoid infinite loop
        currentRow = currentRow + 1
    Loop
    
    ' Auto-fill formulas from B2:G2 down to the last row with data in Column A
    lastRowSummary = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row
    If lastRowSummary >= 2 Then
        wsSummary.Range("B2:G2").AutoFill Destination:=wsSummary.Range("B2:G" & lastRowSummary)
    End If
    
    ' Reset application settings to normal
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    
    MsgBox "PO update completed successfully!", vbInformation
End Sub

Key Fixes & Explanations: #

• Eliminated Infinite Loop Risk: The loop uses currentRow <= lastRowNewPOs as a clear exit condition, and we explicitly increment currentRow each iteration to move through the list.
• Descriptive Variable Names: Replaced generic Sheet5/Sheet2 with wsNewPOs/wsSummary to make the code readable and avoid conflicts with Excel’s default sheet objects.
• Handled Empty Entries: Skips blank cells in New_POs Column A to prevent unnecessary work.
• Efficient PO Lookup: Uses Application.Match instead of a nested loop to check for existing POs—this is faster and cleaner.
• Safe Formula Auto-Fill: Only runs if there’s data below row 2, preventing errors in empty or header-only sheets.

Common Reasons Your Original Do While Failed: #

1. Missing Counter Increment: If you forgot to update currentRow inside the loop, it would run forever on the same row.
2. Incorrect Loop Condition: For example, using Do While Not IsEmpty(...) without accounting for blank rows in your data could stop the loop early.
3. Unqualified Ranges: Failing to specify the worksheet for your range might have made Excel reference the active sheet instead of the intended one.

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