Replace multiple words in single column using VBA

Question

I'm trying to replace some words with others in the same column using VBA. Currently, I'm able to replace single word but I want to replace multiple words at the same time.

Below is code-

Sub replce()
Worksheets("Sheet1").Columns("C").Replace _
 What:="Ryan Group", Replacement:="COS", _
 SearchOrder:=xlByColumns, MatchCase:=True
End Sub

But, I want to replace like

Sub replce()
Worksheets("Sheet1").Columns("C").Replace _
What:= "Ryan Group, Zyan Group, Wayn Group", Replacement:="COS, TAN, SIN"
 SearchOrder:=xlByColumns, MatchCase:=True
End Sub

How can I replace the multiple words? can I implement this by using any other VBA code? Please help. Thanks in advance. Be safe!

Use two arrays, one with the original words, and one with the replacement words, and a loop. — BigBen
– BigBen, Commented Jul 22, 2020 at 21:11
@BigBen yeah, I thought of using the loop. But I'm new in this so, I'm don't really know how to use the loop. — Rushi
– Rushi, Commented Jul 22, 2020 at 21:19
I found too many VB codes but none of them is working for me, this one is simple and working without any error but I just want to use the loop in it. I dont know how to use array and loop — Rushi
– Rushi, Commented Jul 22, 2020 at 21:31

BigBen · Accepted Answer · 2020-07-22 21:37:31Z

4

Use two arrays and a loop:

Sub replce()
    Dim fromList() As Variant
    Dim toList() As Variant
    
    fromList = Array("Ryan Group", "Zyan Group", "Wayn Group")
    toList = Array("COS", "TAN", "SIN")
    
    Dim i As Long
    For i = LBound(fromList) To UBound(fromList)
        Worksheets("Sheet1").Columns("C").Replace _
            What:=fromList(i), Replacement:=toList(i), _
            SearchOrder:=xlByColumns, MatchCase:=True
    Next

End Sub

answered Jul 22, 2020 at 21:37

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

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

Comments

Error 1004 · Accepted Answer · 2020-07-22 21:44:04Z

1

In my opinion you could try the below code:

Sub replce()

    Dim strWhat As String, strRep As String
    Dim arrWhat As Variant, arrRep As Variant
    Dim i As Long
    
    strWhat = "Ryan Group, Zyan Group, Wayn Group"
    strRep = "COS, TAN, SIN"
    
    With ThisWorkbook.Worksheets("Sheet1").Columns("C")
    
        arrWhat = Split(strWhat, ",")
        arrRep = Split(strRep, ",")
        
        For i = LBound(arrWhat) To UBound(arrWhat)
            .Replace What:=Trim(arrWhat(i)), Replacement:=Trim(arrRep(i)), SearchOrder:=xlByColumns, MatchCase:=True
        Next i
        
    End With
    
End Sub

answered Jul 22, 2020 at 21:44

Error 1004

8,2883 gold badges36 silver badges58 bronze badges

Collectives™ on Stack Overflow

Replace multiple words in single column using VBA

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related