Working MUltifindreplace code very slowly

**abjac** · 07-07-2013, 02:29 AM

Hi I have below code which have to replace word/numbers in multi sheets. In below example it takes 2 countries, but it have to be so i can put data in for 6 countries. My only problem with the code is that its really slowly, even if i only in my example have 2 countries.
I need help to make this code much faster, myabe it instead of the range A2:A5000 can be from A2 to last used cell in column A.
But please have a look. I have attached a little test sheet, so you can try on that.

Sincerely
Abjac

My slowly code

Sub multiFindNReplace()
    Dim myRange
    With Sheets("Denmark")
    Set myRange = .Range("A2:A5000") 'range to be searched
    For Each cel In myRange.Columns(1).Cells
       myRange.Replace what:="0149107762", replacement:="0149107762 DKK"
       myRange.Replace what:="3119120012", replacement:="3119120012 DKK"
       myRange.Replace what:="5036013585", replacement:="5036013585 USD"
       myRange.Replace what:="5036073472", replacement:="5036073472 EUR"
       myRange.Replace what:="700406032", replacement:="700406032 DKK"
       Next cel
       End With
       With Sheets("Finland")
    Set myRange = .Range("A2:A5000") 'range to be searched
    For Each cel In myRange.Columns(1).Cells
       myRange.Replace what:="FI3620011800329081", replacement:="FI3620011800329081 EUR"
       myRange.Replace what:="700406032", replacement:="700406032 EUR"
       
       Next cel
       End With
       
       
End Sub

**AlphaFrog** · 07-07-2013, 02:59 AM

No need to loop through each cell.

Sub multiFindNReplace()
    With Sheets("Denmark").Range("A:A")    'range to be searched
        .Replace what:="0149107762", replacement:="0149107762 DKK"
        .Replace what:="3119120012", replacement:="3119120012 DKK"
        .Replace what:="5036013585", replacement:="5036013585 USD"
        .Replace what:="5036073472", replacement:="5036073472 EUR"
        .Replace what:="700406032", replacement:="700406032 DKK"
    End With
    With Sheets("Finland").Range("A:A")    'range to be searched
        .Replace what:="FI3620011800329081", replacement:="FI3620011800329081 EUR"
        .Replace what:="700406032", replacement:="700406032 EUR"
    End With
End Sub

**abjac** · 07-07-2013, 03:10 AM

Hi AlphaFrog. Thanks for the help to this code it changed the speed very much. Infact the result comes right away. Thanks allot really great help.

Have a nice day

Sincerely
Abjac