Excel VBA用数组组替换 [英] Excel VBA Replace with Array Groups

问题描述

现在我正在使用一种效率极低的替换函数：

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

这篇关于Excel VBA用数组组替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！