07 20230325 Replace批量替换字典数组实战

最新推荐文章于 2025-11-01 17:34:18 发布

原创最新推荐文章于 2025-11-01 17:34:18 发布 · 213 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#VBA

VBA业务收集篇专栏收录该内容

9 篇文章

订阅专栏

这两个宏SubMacro1和SubMacro2使用VBA在Excel中执行字符串替换操作。它们分别通过数组和字典数据结构，将特定编码的数字和符号转换为对应的字母。SubRpl子程序则负责实际的替换过程，作用于B列的所有数据。

Sub Macro1()
    Dim a(9) As String, b() As String, x
    a(0) = "０": a(1) = "１": a(2) = "２": a(3) = "３": a(4) = "４"
    a(5) = "５": a(6) = "６": a(7) = "７": a(8) = "８": a(9) = "９"
    
    For i = LBound(a) To UBound(a)
        Call Rpl(a(i), CStr(i))
    Next
    
    Call Rpl(" ", "")
    Call Rpl("Ａ", "A")
    Call Rpl("Ｂ", "B")
    Call Rpl("Ｃ", "C")
    
End Sub
'--------------------------------------------------------------------
Sub Macro2()
    Dim dic As Object, d
    dic(0) = "０": dic(1) = "１": dic(2) = "２": dic(3) = "３": dic(4) = "４"
    dic(5) = "５": dic(6) = "６": dic(7) = "７": dic(8) = "８": dic(9) = "９"
    
    dic(" ") = "": dic("Ａ") = "A": dic("Ｂ") = "B": dic("Ｃ") = "C"

    For Each d In dic.keys
        Call Rpl(d, dic(d))
    Next
    
End Sub
'--------------------------------------------------------------------
Sub Rpl(s1 As String, s2 As String)
    Range("B:B").Replace what:=s1, replacement:=s2, matchBYte:=True, Searchformat:=True, ReplaceFormat:=True
End Sub