Excel VBA Replace Text with Two Arrays

Question

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

Jean-François Corbett · Accepted Answer · 2015-03-19 09:39:51Z

3

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>)

edited Mar 19, 2015 at 9:39

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

answered Mar 19, 2015 at 9:34

shA.t

17k5 gold badges59 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

clippertm Over a year ago

Thanks! I guess it is safer to use UBound, just in case value are added/removed in the array.

Jean-François Corbett · Accepted Answer · 2015-03-19 09:06:01Z

1

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

answered Mar 19, 2015 at 9:06

Jean-François Corbett

38.7k30 gold badges144 silver badges192 bronze badges

1 Comment

clippertm Over a year ago

Thanks Jean-François! Next was the missing part :)

Collectives™ on Stack Overflow

Excel VBA Replace Text with Two Arrays

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related