Excel VBA Replace with Array Groups

Question

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

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!

brettdj · Accepted Answer · 2015-03-20 04:16:07Z

0

Your Replace(s) are fine - its the cell by cell loop and selection that is inefficient. Try something like this for three replaces over the entire range at once.

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

answered Mar 20, 2015 at 4:16

brettdj

55.9k16 gold badges120 silver badges178 bronze badges

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

Comments

hpf · Accepted Answer · 2015-03-20 04:24:31Z

0

I would try this:

aWhat=Split("ABC|ABC|DEF|DEF|GHI|GHI...","|")
aReplacement=Split("123|456|789|.....","|")
For i=1 to UBound(aWhat)
  rngRepVal.Replace what:=aWhat[i], Replacement:=aReplacement[i], ....
Next i

Just make sure there's the same number of elements in both arrays.

answered Mar 20, 2015 at 4:24

hpf

4282 silver badges9 bronze badges

Comments

clippertm · Accepted Answer · 2015-03-20 06:10:44Z

0

I think found it, by accident:

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

answered Mar 20, 2015 at 6:10

clippertm

1596 silver badges18 bronze badges

Collectives™ on Stack Overflow

Excel VBA Replace with Array Groups

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related