CODEWITHSUNDEEP
excelvbasearchreplacefind
Ross G 1209
Ross G 1209

Reputation: 1

Multiple Find & Replace

I've trawled this site but not found anything exact for my issue.

I have an excel file (Sheet1) containing various foreign characters (eg. "ä" or "Ö") within multiple columns and rows (basically the entire sheet).

In Sheet2 I have a list/table (Columns A & B) of these characters (A) and what they need to be replaced with (B).

I would like a VBA code to search for all of these characters in Sheet1 and replace them all with the corresponding alternative.

Any help would be amazing.

Thanks,

Upvotes: 0

Views: 227

Answers (2)

Justyna MK
Justyna MK

Reputation: 3563

Ross, your solution almost did the trick. You can simplify it by using:

Sub ReplaceChar()
    Dim Sh1 As Worksheet
    Dim Sh2 As Worksheet
    Dim cel As Range

    Set Sh1 = Sheets(1)
    Set Sh2 = Sheets(2)

    For Each cel In Sh2.Columns(1).SpecialCells(2)
        With Sh1.Cells
            .Replace What:=cel, Replacement:=cel.Offset(, 1), MatchCase:=True
        End With
    Next
End Sub

Sheet1 and Sheet2 before running the macro:

enter image description here

enter image description here

...and here's Sheet1 after running the code:

enter image description here

Upvotes: 2

Davesexcel
Davesexcel

Reputation: 6984

Experience has shown me that upper & lower case chars are treated the same when using find and replace.

The below sample uses column Q as the character to find and column R as the replacement. The code does the find replace for cells in column A

Sub Using_Replace()
    Dim LstRw As Long, rng As Range, c As Range

    LstRw = Cells(Rows.Count, "Q").End(xlUp).Row
    Set rng = Range("Q1:Q" & LstRw)

    For Each c In rng.Cells

        Range("A:A").Replace what:=c, Replacement:=c.Offset(, 1), _
                             LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
                             SearchFormat:=False, ReplaceFormat:=False
    Next c

End Sub

enter image description here

When you test out the code you will notice the find/replace does not notice the difference between the characters in Q6 & Q15.

But if you loop through each cell, the differences are recognized.

-

Sub Using_LikeLoop()
    Dim LstRw As Long, rng As Range, c As Range
    Dim frNg As Range, f As Range

    LstRw = Cells(Rows.Count, "Q").End(xlUp).Row
    Set rng = Range("Q1:Q" & LstRw)
    Set frNg = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)

    For Each c In rng.Cells

        For Each f In frNg.Cells

            If f Like "*" & c & "*" Then f = Replace(f, c, c.Offset(, 1))

        Next f

    Next c

End Sub

Upvotes: 0

Related Questions