Excel 批量 替换 宏 VBA excel 用vba 写一个批量公式替换的宏

\u5e2e\u5199\u4e2aVBA\u5b8f\uff0cexcel\u7528\u7684\u6279\u91cf\u66ff\u6362\u5b8f

\u5982\u679c\u786e\u5b9aA\u5217\u4e2d\u6240\u6709\u4e0d\u89c4\u5219\u62ec\u53f7\u90fd\u5728\u5b57\u7b26\u4e32\u7684\u9996\u5c3e\u7684\u8bdd\uff0c\u7684\u786e\u4e0d\u9700\u8981\u5b8f\uff0c\u76f4\u63a5\u7528\u516c\u5f0f\u5373\u53ef\uff1a


B1\uff1a="["&MID(A1,2,LEN(A1)-2)&"]"


MID\u51fd\u6570\u5c06A\u5217\u4e2d\u7684\u5b57\u7b26\u4e32\u4ece\u7b2c2\u4e2a\u5b57\u7b26\u8d77\uff0c\u622a\u53d6\u6574\u4e2a\u5b57\u7b26\u4e32\u957f\u5ea6\u51cf2\u5b57\u7b26\uff0c\u5373\u6390\u5934\u53bb\u5c3e\u53d6\u62ec\u53f7\u4e2d\u95f4\u7684\u5185\u5bb9\uff0c\u7136\u540e\u4e24\u5934\u52a0\u4e0a\u89c4\u5219\u62ec\u53f7\u5373\u53ef\u3002

\u8bf4\u6e05\u695a\u4e00\u70b9\u3002

\u76f4\u63a5=y!A1\u6539\u4e3aA1\uff0c\u4e0b\u62c9\u586b\u5145\u3002

Sub 替换()
    Dim Ar, I As Long
    Ar = Worksheets(2).UsedRange
    For I = 1 To UBound(Ar)
        Worksheets(1).Cells.Replace Ar(I, 1), Ar(I, 2), xlPart, , False
    Next
End Sub

两个a选哪个？？？

都是唯一的对应值，代码如下

Sub test()
    Dim arr, brr, x&, y&
    With Sheet1
        arr = .Range("A1:A" & .Range("A65536").End(xlUp).Row)
    End With
    With Sheet2
        brr = .Range("A1:B" & .Range("A65536").End(xlUp).Row)
    End With
    For x = 1 To UBound(arr)
        For y = 1 To UBound(brr)
            arr(x, 1) = Replace(UCase(arr(x, 1)), UCase(brr(y, 1)), brr(y, 2))
        Next y
    Next x
    Sheet1.Range("A1").Resize(UBound(arr)) = arr
End Sub

代码如下：

Sub RP()
Dim KW$()
'-------读取替换表-------
KW = Sheet2.Cells(1, 1).CurrentRegion.Value
If UBound(KW, 2) > 2 Then MsgBox ("Sheet2表中超过2例数据！")
'--------进行替换---------
For i = 1 To Sheet1.Cells(1, 1).CurrentRegion.Rows.Count
tmp = Sheet1.Cells(i, 1).Value
For j = 1 To UBound(KW, 1)
tmp = Replace(UCase(tmp), UCase(KW(j, 1)), KW(j, 2))
Next j
Sheet1.Cells(i, 1).Value = tmp
Next i
End Sub

 

 

扩展阅读：excel 重复人名 计数 ... 表格一键替换不同数据 ... 批量替换多个不同内容 ... 怎么批量替换2022至2023 ... 批量替换为不同的姓名 ... excel 批量替换 不同值 ... excel快速批量替换 ... excel用宏实现批量替换 ... 大量数据多对多批量替换 ...