手动使用WECHSELN函数效率低，转VBA宏后代码格式异常求助

嘿，我明白你的困扰——手动用嵌套的WECHSELN函数处理2万行数据确实慢得离谱，转VBA又碰到代码自动换行的坑，这就帮你搞定！

首先解决代码换行的问题 #

你遇到的代码被拆分到多行的情况，是因为VBA对单行字符串的长度有上限，直接粘贴超长公式会触发自动换行，导致语法错误。解决办法是用续行符_（注意前面必须加空格）把长公式拆成多段，再用&拼接起来，这样代码就能正常识别了。

修正后的VBA代码示例（补全了你没写完的部分逻辑）：

Sub ApplySubstituteFormula()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Tabelle1")
    
    ' 拆分超长公式为多个片段，用续行符连接
    Dim longFormula As String
    longFormula = "=WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(" & _
                  "WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(" & _
                  "WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(" & _
                  "WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(" & _
                  "WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(" & _
                  "WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(WECHSELN(" & _
                  "WECHSELN(WENN(G2=""___"";""___"";GROSS2(F2)); ""Mr "";""""); ""Mrs "";""""); ""Dr. "";""""); ""Mba "";""""); ""Di "";"""")" & _
                  "; ""MSc "";""""); ""Msc "";""""); ""Kr "";""""); ""Gp%"";""general partner""); ""mbh"";""mbH""); ""Mbh"";""mbH"")" & _
                  "; ""M.B.H."";""m.b.H.""); "" Kg"";"" KG""); "" Ag "";"" AG """); "" Og "";"" OG """); "" Sa "";"" SA """)" & _
                  "; "" Se "";"" SE """); "" Ab "";"" AB """); "" Inc"";"" Inc.""); "" Ltd"";"" Ltd.""); ""D.O.O."";""d.o.o."")" & _
                  "; ""S.R.L."";""S.r.l.""); ""S.Ar.l."";""SARL""); "" Sarl"";""SARL"")"
    
    ' 将公式应用到指定范围
    ws.Range("H2:H20001").FormulaLocal = longFormula
End Sub

进阶提速：放弃公式，用VBA直接处理值 #

其实就算用VBA写公式，2万行的计算量还是不小。更高效的方式是直接用VBA的Replace函数处理单元格值，跳过Excel的公式计算环节，速度能快好几倍！

下面是优化后的代码，用数组批量处理数据（数组操作是VBA提速的关键）：

Sub FastSubstitute()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim dataArr As Variant
    Dim i As Long
    Dim cellValue As String
    
    Set ws = ThisWorkbook.Worksheets("Tabelle1")
    ' 自动获取G列最后一行，比固定写20001更灵活
    lastRow = ws.Cells(ws.Rows.Count, "G").End(xlUp).Row
    ' 把F、G列数据读到数组里，减少和Excel界面的交互
    dataArr = ws.Range("F2:G" & lastRow).Value
    
    ' 循环处理每一行数据
    For i = LBound(dataArr, 1) To UBound(dataArr, 1)
        ' 先处理WENN的逻辑：G列是___就返回___，否则把F列转大写（对应GROSS2）
        If dataArr(i, 2) = "___" Then
            cellValue = "___"
        Else
            cellValue = UCase(dataArr(i, 1))
        End If
        
        ' 执行所有替换操作，对应原来的WECHSELN
        cellValue = Replace(cellValue, "Mr ", "")
        cellValue = Replace(cellValue, "Mrs ", "")
        cellValue = Replace(cellValue, "Dr. ", "")
        cellValue = Replace(cellValue, "Mba ", "")
        cellValue = Replace(cellValue, "Di ", "")
        cellValue = Replace(cellValue, "MSc ", "")
        cellValue = Replace(cellValue, "Msc ", "")
        cellValue = Replace(cellValue, "Kr ", "")
        cellValue = Replace(cellValue, "Gp%", "general partner")
        cellValue = Replace(cellValue, "mbh", "mbH")
        cellValue = Replace(cellValue, "Mbh", "mbH")
        cellValue = Replace(cellValue, "M.B.H.", "m.b.H.")
        cellValue = Replace(cellValue, " Kg", " KG")
        cellValue = Replace(cellValue, " Ag ", " AG ")
        cellValue = Replace(cellValue, " Og ", " OG ")
        cellValue = Replace(cellValue, " Sa ", " SA ")
        cellValue = Replace(cellValue, " Se ", " SE ")
        cellValue = Replace(cellValue, " Ab ", " AB ")
        cellValue = Replace(cellValue, "Inc", "Inc.")
        cellValue = Replace(cellValue, "Ltd", "Ltd.")
        cellValue = Replace(cellValue, "D.O.O.", "d.o.o.")
        cellValue = Replace(cellValue, "S.R.L.", "S.r.l.")
        cellValue = Replace(cellValue, "S.Ar.l.", "SARL")
        cellValue = Replace(cellValue, " Sarl", "SARL")
        ' 如果还有其他替换项，在这里继续添加
        
        ' 把处理后的值存回数组
        dataArr(i, 1) = cellValue
    Next i
    
    ' 一次性把数组写回H列，速度超快
    ws.Range("H2:H" & lastRow).Value = dataArr
End Sub

小提示 #

1. 如果必须保留公式（方便后续修改），用第一个方法；如果只需要最终结果，第二个方法绝对是速度王者。
2. 测试的时候先选100行数据试跑，确保替换逻辑和原来的WECHSELN完全一致，再批量处理2万行。

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