基于ISBLANK条件自动填充序列号的VBA宏出现“Compile Error: Argument Not Optional”编译错误的解决咨询

Let's break down what's going wrong and fix this step by step:

The Root Cause of the Compile Error #

Your line Range.Value is invalid VBA syntax. The Value property needs an assignment operation to work—you can't just call it on its own. What you're trying to do here is convert the formulas to plain values, which requires setting the range's value equal to itself (overwriting formulas with their calculated results).

Corrected Macro Code #

Sub SerialNo()
    Dim LastRow As Long
    ' Get the last non-empty row in column B
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    With Range("A1:A" & LastRow)
        ' Apply the serial number formula
        .FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",COUNTA(R1C2:RC[1]))"
        ' Convert formulas to static values
        .Value = .Value
    End With
End Sub

Key Fixes & Explanations #

• Fixed the formula-to-value line: Replaced the invalid Range.Value with .Value = .Value—the dot references the range we're working with in the With block, and this assignment replaces the formulas with their computed values.
• Kept your core logic intact: The original formula for generating serial numbers (only when column B isn't blank) stays the same, so it will still count non-empty cells in column B up to each row.

Optional: Add Efficiency Improvements #

If you're working with large datasets, adding these lines can speed up the macro by disabling screen updates and events temporarily:

Sub SerialNo()
    Dim LastRow As Long
    
    ' Disable screen updates and events for speed
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    
    LastRow = Cells(Rows.Count, "B").End(xlUp).Row
    
    With Range("A1:A" & LastRow)
        .FormulaR1C1 = "=IF(ISBLANK(RC[1]),"""",COUNTA(R1C2:RC[1]))"
        .Value = .Value
    End With
    
    ' Re-enable screen updates and events
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

This revised macro will run without compile errors, generate the serial numbers as intended, and convert all formulas in column A to static values so the formulas are hidden.

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