VBA中For循环添加TEXTAFTER公式时，符号计数函数异常排查

核心问题：自定义计数函数未返回结果 #

你的CNT_SYMBOLS函数仅计算了符号数量，但未将结果赋值给函数本身，导致函数始终返回默认值0，这直接导致IF/ELSE判断完全失效。

修复后的计数函数 #

Function CNT_SYMBOLS(str As String, character As String) As Integer
    ' 处理空字符串的极端情况
    If str = "" Then
        CNT_SYMBOLS = 0
        Exit Function
    End If
    ' 将计算结果赋值给函数，完成返回
    CNT_SYMBOLS = UBound(Split(str, character))
End Function

优化后的循环代码 #

原循环存在冗余操作（重复初始化rngStart、使用Select降低效率），简化后代码如下：

Dim row As Long
Dim rngStart As Range
Set rngStart = wsTest.Range("A1") ' 移至循环外，避免重复赋值

For row = 1 To rngThird.Rows.Count
    Dim targetCell As Range, sourceCell As Range
    Set targetCell = rngStart.Offset(row, 0)
    Set sourceCell = rngStart.Offset(row, 1)
    
    Dim strTAFormula As String
    If CNT_SYMBOLS(targetCell.Value, "-") >= 2 Then
        strTAFormula = "=TRIM(TEXTAFTER(" & sourceCell.Address & ",""-"",2,1,1," & sourceCell.Address & "))"
    Else
        strTAFormula = "=TRIM(TEXTAFTER(" & sourceCell.Address & ",""-"",1,1,1," & sourceCell.Address & "))"
    End If
    targetCell.Formula = strTAFormula ' 直接赋值，无需选中单元格
Next row

替代方案：无需自定义函数的计数方式 #

如果你的Excel版本支持，也可以直接用LEN+SUBSTITUTE组合计算符号数量，避免自定义函数的潜在问题：

' 替换原CNT_SYMBOLS的调用逻辑
Dim dashCount As Integer
dashCount = Len(targetCell.Value) - Len(Replace(targetCell.Value, "-", ""))
If dashCount >= 2 Then
    ' 执行对应公式赋值逻辑
End If

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