如何让VBA代码在目标表头不存在时跳过执行并继续下一项？

Got it, let's fix this code so it skips any headers from WS2 ("Bckg") that don't exist in WS1 ("Main")'s first row. The original code has a lot of repetitive lines and will fail silently (or throw unhandled errors) if a header isn't found—here's a cleaner, more robust version:

Sub TransData4Headers()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim headerCol As Range
    Dim matchResult As Variant
    Dim targetCol As Long
    
    ' Check if required sheets exist first
    On Error Resume Next
    Set WS1 = ThisWorkbook.Sheets("Main")
    Set WS2 = ThisWorkbook.Sheets("Bckg")
    On Error GoTo 0
    
    If WS1 Is Nothing Or WS2 Is Nothing Then
        MsgBox "Required sheets ('Main' or 'Bckg') not found!", vbExclamation
        Exit Sub
    End If
    
    ' Loop through each header column in WS2 (AG1 to BC1)
    For Each headerCol In WS2.Range("AG1:BC1").Columns
        ' Get the header text to match
        Dim headerText As String
        headerText = headerCol.Value
        
        ' Skip if header cell is empty
        If headerText = "" Then GoTo NextColumn
        
        ' Use Application.Match to avoid runtime errors if header isn't found
        matchResult = Application.Match(headerText, WS1.Rows(1), 0)
        
        ' Check if match was successful
        If Not IsError(matchResult) Then
            targetCol = matchResult
            ' Assign value from WS2 row 2 to the next empty row in WS1's target column
            WS1.Cells(WS1.Rows.Count, targetCol).End(xlUp).Offset(1, 0).Value = _
                WS2.Cells(2, headerCol.Column).Value
        End If
        
NextColumn:
    Next headerCol
    
    MsgBox "Data transfer completed (missing headers skipped)!", vbInformation
End Sub

Key Improvements & Explanations: #

• Replaced repetitive code with a loop: Instead of declaring 22 separate range variables and writing identical Match/assign logic 22 times, we loop through the target columns (AG to BC) in WS2. This makes the code shorter, easier to update, and less error-prone.
• Safe header matching with Application.Match: Unlike WorksheetFunction.Match, which throws a runtime error if the header isn't found, Application.Match returns an error value we can check with IsError(). This lets us cleanly skip missing headers without relying on global error suppression.
• Added sheet existence check: The original code would crash if "Main" or "Bckg" sheets don't exist. Now we validate the sheets first and show a user-friendly message if they're missing.
• Removed unnecessary range variables: We directly reference the data cell (row 2) for each column in the loop, eliminating the need for 22 redundant Rng variables.
• Removed global On Error Resume Next: The original code used this to hide errors, which can mask other issues (like typos in sheet names). Now we only use error handling temporarily to check for sheet existence, and handle missing headers explicitly.

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