使用VBA设置Excel列格式及公式时触发RunTime Error 1004

原代码第一个错误原因 #

.Range("N2:N")属于不合法的单元格范围引用，Excel无法解析这种仅指定起始单元格和列、未明确结束位置的写法，这就是触发RunTime Error 1004的直接原因。必须为Range指定完整的起止范围，比如结合数据最后一行来定义。

修改后公式行错误原因 #

最后一个公式字符串"=RC[-2]-RC[-1])"末尾多了一个冗余的右括号)，导致公式语法非法，Excel无法执行该公式。

修正后的完整代码 #

Sub Format_Raw_SSPData()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    Set ws = Sheets("Raw SSP Data")
    
    ' 处理D列
    With ws
        .Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        .Range("D2:D" & lastRow).FormulaR1C1 = "=RIGHT(RC[-1],4)"
    End With
    
    ' 处理N列
    With ws
        .Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        lastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
        .Range("N2:N" & lastRow).NumberFormat = "m/d/yyyy"
        .Range("N2:N" & lastRow).FormulaR1C1 = "=INT(RC[-1])"
    End With
    
    ' 处理O列
    With ws
        .Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        lastRow = .Cells(.Rows.Count, "N").End(xlUp).Row
        .Range("O2:O" & lastRow).NumberFormat = "hh:mm"
        .Range("O2:O" & lastRow).FormulaR1C1 = "=RC[-2]-RC[-1]"
    End With
End Sub

额外优化建议 #

可以将三次With块合并为一个，减少重复代码，提升可读性：

Sub Format_Raw_SSPData()
    Dim ws As Worksheet
    Dim lastRow As Long
    
    Set ws = Sheets("Raw SSP Data")
    
    With ws
        ' 处理D列
        .Columns("D:D").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        lastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
        .Range("D2:D" & lastRow).FormulaR1C1 = "=RIGHT(RC[-1],4)"
        
        ' 处理N列
        .Columns("N:N").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        lastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
        .Range("N2:N" & lastRow).NumberFormat = "m/d/yyyy"
        .Range("N2:N" & lastRow).FormulaR1C1 = "=INT(RC[-1])"
        
        ' 处理O列
        .Columns("O:O").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
        lastRow = .Cells(.Rows.Count, "N").End(xlUp).Row
        .Range("O2:O" & lastRow).NumberFormat = "hh:mm"
        .Range("O2:O" & lastRow).FormulaR1C1 = "=RC[-2]-RC[-1]"
    End With
End Sub

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