VBA宏需求：出错显示空值并续行，D列空则E列对应为空

To address your requirements—leaving E column cells empty when D column cells are blank or when division errors occur—here are two straightforward fixes for your VBA macro:

Formula-Based Approach (Efficient) #

Update your macro to use a combined formula that checks for blank cells first and catches calculation errors:

Sub MyMacro()
    ' Apply formula to the entire range in one go
    Range("E9:E20").Formula = "=IF(ISBLANK(D9), """", IFERROR(1/D9, """"))"
End Sub

Key Details: #

• ISBLANK(D9): Checks if the corresponding D column cell is empty, returns an empty string if true
• IFERROR(1/D9, ""): Catches errors like division by zero, returning an empty string instead of an error value
• Double quotes "" in the VBA string represent a single quote in the final Excel formula
• Always use English function names in VBA's .Formula property, even if your Excel uses a localized language

Loop-Based Approach (Explicit Control) #

If you prefer a more hands-on method for additional logic flexibility, iterate through each cell individually:

Sub MyMacroLoop()
    Dim ws As Worksheet
    Dim cell As Range
    Set ws = ActiveSheet ' Replace with your worksheet name if needed, e.g., ThisWorkbook.Worksheets("Data")
    
    For Each cell In ws.Range("E9:E20")
        Dim dCell As Range
        Set dCell = ws.Cells(cell.Row, "D")
        
        ' Handle blank D cell
        If IsEmpty(dCell.Value) Then
            cell.Value = ""
        Else
            ' Attempt calculation and handle errors
            On Error Resume Next
            cell.Value = 1 / dCell.Value
            If Err.Number <> 0 Then cell.Value = ""
            On Error GoTo 0
        End If
    Next cell
End Sub

Common Pitfalls From Your Previous Attempts: #

• Using localized function names (non-English) in VBA's .Formula property (Excel won’t recognize them)
• Forgetting to escape quotes with double quotes when building formula strings in VBA
• Not combining both blank check and error handling logic in a single formula

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