CODEWITHSUNDEEP
arraysstringexcelvbareplace
clippertm
clippertm

Reputation: 159

Excel VBA Replace Text with Two Arrays

I would like to replace the values in column A:A using arrays:

Dim aValueNew() As String
Dim aValueOld() As String

aValueNew = Split("ABC,DEF,GHI", ",")
aValueOld = Split("123,456,789", ",")

123 needs to be replaced by ABC, 456 by DEF and so forth.

What is the most efficient way of doing this? I am struggling on how to include the Replace function in a loop and your help would be appreciated. Something like:

For i = 0 to i = 2
Range("A:A").Replace What:= aValueOld(i), Replacement:=aValueNew(i), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

Upvotes: 3

Views: 3564

Answers (2)

shA.t
shA.t

Reputation: 16968

Try this code:

For i = 0 to UBound(aValueOld)
    Columns("A:A").Select
    Selection.Replace What:= aValueOld(i), Replacement:=aValueNew(i), LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False

Note:
Your For statement was wrong
If count of values are the bound of array use UBound(<Array Name>)

Upvotes: 3

Jean-Fran&#231;ois Corbett
Jean-Fran&#231;ois Corbett

Reputation: 38550

You almost had it. You may find the documentation for For...Next helpful.

Dim i As Long
For i = 0 To 2
    Range("A:A").Replace What:=aValueOld(i), Replacement:=aValueNew(i), _
        LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False
Next

Upvotes: 1

Related Questions