请求修正VBA代码：基于E列NA值自动填充D列内容

Let's get your code working exactly as you need it—here's the corrected version plus breakdowns of the key changes:

Sub Rules1()
    ' Autofill based on Rules
    Dim sht As Worksheet
    Dim lastRow As Long
    Dim i As Long
    
    ' Set the worksheet directly (no need to activate it first)
    Set sht = ActiveWorkbook.Worksheets("ORD_CS")
    
    ' Get the actual last row with data in column E (more reliable than UsedRange)
    lastRow = sht.Cells(sht.Rows.Count, "E").End(xlUp).Row
    
    With sht
        ' Loop from row 8 to the last used row in column E
        For i = 8 To lastRow
            ' Check if cell in column E is either text "NA" or Excel's #N/A error
            If .Range("E" & i).Value = "NA" Or IsError(.Range("E" & i).Value) Then
                .Range("D" & i).Value = "Create/Map"
            End If
        Next i
    End With
End Sub

Key Fixes & Improvements: #

• Corrected Target Column: Your original code checked column H (Range("H" & i)), but you specified column E—we swapped that to .Range("E" & i) to match your requirement.
• Removed Unnecessary Activation: Activating worksheets can cause errors if the sheet isn't visible or if you switch tabs mid-run. We directly reference the sheet object instead, which is more stable and efficient.
• Reliable Last Row Calculation: UsedRange.Rows.Count can sometimes include empty rows Excel still counts as "used". Using sht.Cells(sht.Rows.Count, "E").End(xlUp).Row finds the actual last row with data in column E.
• Handles Both Text "NA" and #N/A Errors: If your "NA" values are either plain text or Excel error values (like from VLOOKUP failures), this code catches both cases. If you only need to handle text "NA", you can remove the Or IsError(...) part.

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