Excel VBA用数组组替换
问题描述：
现在我正在使用一种效率极低的替换函数：
Right now I am using a horribly inefficient way for a replacement function:
Dim Replacement As String
Dim rngRepVal As Object
Set rngRepVal = Sheets("data").Range(Cells(1, 3), Cells(intRowLast, 3))
Replacement = ActiveCell.Value
rngRepVal.Replace What:="123", Replacement:="ABC", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
rngRepVal.Replace What:="234", Replacement:="ABC", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
rngRepVal.Replace What:="456", Replacement:="DEF", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
... [goes on for 50 lines]
Set rngRepVal = Nothing
I am wondering if this can be achieved with arrays. Something like:
Dim aWhat() As String
Dim aReplacement() As String
aWhat = Split("ABC|DEF|GHI|JKL", "|")
aReplacement = Split(Array("123", 456")|Array("789","1000"), "|") '<-not sure how to organise this
基本上123和456被ABC取代，在替换循环中将789和1000替换为DEF等。>关于如何组织两个数组的任何见解？谢谢！
Essentially 123 & 456 get replaced by ABC, 789 & 1000 get replaced by DEF etc. in a replace loop> Any insights on how to organise the two arrays? Thanks!
答
我认为是偶然发现的：
Dim aOld() As Variant
Dim aNew() As Variant
Dim Group As Variant
Dim Word As Variant
Dim y As Long
aNew = Array("ABC", "DEF", "GHI", "JKL")
aOld = Array(Array("123", "456"), Array("789", "1000"))
With Range("A:A")
For Each Group In aOld
For Each Word In Group
.Replace What:=Word, Replacement:=aNew(y), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next
y = y + 1
Next
End With