arrays - Excel VBA 替换为数组组

Question

现在我正在使用一种非常低效的替换功能:

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

我想知道这是否可以通过数组来实现。就像是:

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 等取代。在替换循环中>关于如何组织这两个数组的任何见解？谢谢!

Answer 1

您的 Replace (s) 很好 - 它的逐个单元循环和选择效率低下。一次在整个范围内尝试这样的三个替换。

Sub Recut()
Dim rng1 As Range
Set rng1 = Sheets("data").Range(Sheets("data").Cells(1, 3), Sheets("data").Cells(Rows.Count, 3).End(xlUp))
With rng1
    .Replace What:="123", Replacement:="ABC", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
    .Replace What:="234", Replacement:="ABC", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
    .Replace What:="456", Replacement:="DEF", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
End With
End Sub