Excel VBA及公式提取两个“-”间9位数字的报错修复求助

1 报错根因说明 #

• 公式#VALUE错误原因：
  1. 原SEARCH函数参数逻辑错误：SEARCH第三个参数为查找起始位置，原公式错误将长度计算逻辑嵌套到该参数位，语法本身不成立；同时公式存在括号未闭合、参数多输入无效字符的问题
  2. 未做异常兼容：当单元格中不存在至少2个-、或两个-之间的内容不是9位数字时，都会触发计算错误
• VBA运行时错误13（类型不匹配）原因：
  1. VBA中字符串内部的双引号未转义：需要用两个双引号""代替单个双引号才能被识别为字符串内容，否则会被判定为字符串结束符，导致语法解析异常触发类型不匹配
  2. 公式拼接错误：换行书写公式没有用连接符拼接，且公式开头多了无效空格

2 修复方案 #

方案1：修正公式+VBA赋值写法 #

修正后可直接运行的VBA代码如下，公式增加了异常兼容，不符合规则的内容会返回空值避免报错：

Sub Data2()
    Dim lastrow As Long
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    Range("F2:F" & lastrow).Formula = "=IFERROR(IF(LEN(MID(A1,SEARCH(""-"",A1)+1,SEARCH(""-"",A1,SEARCH(""-"",A1)+1)-SEARCH(""-"",A1)-1))=9,VALUE(MID(A1,SEARCH(""-"",A1)+1,SEARCH(""-"",A1,SEARCH(""-"",A1)+1)-SEARCH(""-"",A1)-1)),"""")"
End Sub

方案2：纯VBA原生处理（性能更优） #

无需写入单元格公式，直接遍历A列提取结果写入F列，兼容性更高：

Sub Data2()
    Dim lastrow As Long, i As Long
    Dim arr As Variant, splitArr As Variant
    lastrow = Range("A" & Rows.Count).End(xlUp).Row
    arr = Range("A1:A" & lastrow).Value
    ReDim res(1 To UBound(arr), 1 To 1)
    ' 从第2行开始遍历，可根据是否有表头调整起始行
    For i = 2 To UBound(arr)
        splitArr = Split(arr(i, 1), "-")
        ' 校验是否存在至少2个分隔符，且第二个分段为9位数字
        If UBound(splitArr) >= 1 Then
            If Len(splitArr(1)) = 9 And IsNumeric(splitArr(1)) Then
                res(i, 1) = CLng(splitArr(1))
            End If
        End If
    Next
    Range("F1:F" & lastrow).Value = res
End Sub

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