VBA中Range.Formula调用LEN、RIGHT、LEFT函数的问题求助

先帮你梳理下代码里的问题，以及更高效的实现方式：

你的代码存在的潜在问题 #

• 未声明变量：LR、i、cel这些变量都没做声明，在VBA里容易引发类型匹配错误，建议开头加上Option Explicit强制变量声明，能提前排查很多隐性问题
• 循环逐个写公式效率低：如果数据量较大，这种逐单元格写入的方式会很慢，Excel支持整列批量写入公式，速度提升非常明显
• 公式拼接冗余：你把2和6单独用&拼接，其实可以直接嵌入字符串里，写法更简洁
• 行号范围可能有误：如果第一行是表头，循环从1开始会把公式写到表头行，导致引用非数值单元格出现#VALUE!错误

修正后的代码方案 #

方案1：优化你的循环写法 #

如果习惯用循环实现，修正后的代码如下：

Option Explicit

Sub WriteFormulas()
    Dim LR As Long
    Dim i As Long
    Dim celA As String, celQ As String
    
    ' 获取A列最后一行的行号
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 从第2行开始（假设第1行是表头），如果没有表头就改成1
    For i = 2 To LR
        celA = "A" & i
        celQ = "Q" & i
        ' 写入P列的LEN统计公式
        Range("P" & i).Formula = "=LEN(" & celA & ")"
        ' 写入J列的RIGHT+LEFT截取公式，简化拼接逻辑
        Range("J" & i).Formula = "=RIGHT(LEFT(" & celA & "," & celQ & "-2),6)"
    Next i
End Sub

方案2：更高效的批量写入公式（推荐） #

直接给整列批量设置公式，不需要循环，速度快得多，Excel会自动把公式中的单元格引用对应到每一行：

Option Explicit

Sub WriteFormulasBatch()
    Dim LR As Long
    
    LR = Cells(Rows.Count, "A").End(xlUp).Row
    
    ' 批量写入P列的LEN公式
    Range("P2:P" & LR).Formula = "=LEN(A2)"
    ' 批量写入J列的截取公式
    Range("J2:J" & LR).Formula = "=RIGHT(LEFT(A2,Q2-2),6)"
End Sub

原代码异常的常见原因 #

如果原代码运行报错，大概率是这两个情况：

1. 循环从第1行开始，而第1行是表头，导致公式引用了非数值的表头单元格（比如Q1-2会产生#VALUE!错误）
2. 未声明变量导致的隐性类型错误，比如LR被识别为变体类型，在某些场景下获取行号出错

测试时可以先手动在单元格输入公式确认能正常计算，再用VBA批量写入，这样更容易定位问题。

内容的提问来源于stack exchange，提问作者Rafael Rodrigues Santos