Excel VBA中Range.Formula设置公式报错求助，求优化方案

先帮你揪出VBA代码里的公式问题，主要是两个关键错误导致无法运行：

1. 引号转义错误：在VBA字符串里要插入双引号，得用两个双引号""来转义。你写的FIND(:" & cel4 & ")里的冒号没加引号，正确写法应该是FIND("":"", " & cel4 & ")
2. 公式分隔符不匹配：你的目标公式用了分号;，但VBA的.Formula属性默认用英文逗号,当参数分隔符（对应英文区域）。如果你的Excel是用分号做分隔符的区域（比如欧洲语言版本），得改用.FormulaLocal属性

修正后的VBA代码 #

LR = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To LR
    cel4 = "X" & i
    cel3 = "Z" & i
    ' 英文区域Excel（逗号分隔符）用这行
    Range("M" & i).Formula = "=RIGHT(LEFT(" & cel4 & "," & cel3 & "-2),LEN(LEFT(" & cel4 & "," & cel3 & "-2))-FIND("":""," & cel4 & "))"
    ' 非英文区域（分号分隔符）替换成下面这行
    ' Range("M" & i).FormulaLocal = "=RIGHT(LEFT(" & cel4 & ";" & cel3 & "-2);LEN(LEFT(" & cel4 & ";" & cel3 & "-2))-FIND("":""," & cel4 & "))"
Next i

更优解决思路 #

1. 简化公式（更易读，逻辑一致） #

你的原公式嵌套了多层LEFT/RIGHT，其实可以改成更简洁的MID写法，效果完全一样：

=MID(X1,FIND(":",X1)+1,Z1-FIND(":",X1)-2)

这个公式直接从第一个冒号的下一位开始提取，提取长度是Z1-2减去冒号的位置，逻辑更清晰。

对应VBA写法：

Range("M" & i).Formula = "=MID(" & cel4 & ",FIND("":""," & cel4 & ")+1," & cel3 & "-FIND("":""," & cel4 & ")-2)"

2. 直接用VBA处理字符串（跳过公式，效率更高） #

如果不需要保留公式，直接计算结果写入单元格，速度会快很多（尤其数据量大的时候）：

LR = Cells(Rows.Count, "A").End(xlUp).Row
Dim xText As String, colonPos As Long, endPos As Long
For i = 1 To LR
    xText = Range("X" & i).Value
    colonPos = InStr(xText, ":") ' 找到第一个冒号的位置
    endPos = Range("Z" & i).Value - 2
    ' 做个简单的合法性判断，避免出错
    If colonPos > 0 And endPos > colonPos Then
        Range("M" & i).Value = Mid(xText, colonPos + 1, endPos - colonPos)
    Else
        Range("M" & i).Value = "" ' 不符合条件时留空
    End If
Next i

3. Excel 365专属极简方案 #

如果你用的是Excel 365，直接用新出的文本函数一步搞定：

=TEXTAFTER(TEXTBEFORE(X1, "", Z1-2), ":")

先截取X1到Z1-2位置的文本，再提取冒号之后的内容，可读性拉满。

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