CODEWITHSUNDEEP
excelvba
Josef Miller
Josef Miller

Reputation: 113

Enhancing excel multiple find and replace script

I am using the following code to find and replace a list of words that exist in Sheet1 (ColA for FIND words, and ColB for REPLACE words) within the range A:H of Sheet2. It executes the job but very slowly. Can this script be modified to run faster?

Option Explicit
Option Base 1
Sub FindReplace()
Dim LR&, Ctr&
Dim ArrayInsen As Variant
Application.ScreenUpdating = False
With Sheets("Sheet1")
    LR& = Cells(Rows.Count, "A").End(xlUp).Row
    ArrayInsen = Worksheets("Sheet1").Range("A2:B" & LR&)
End With
With Sheets("Sheet2").Columns("A:H")
    Application.DisplayAlerts = False
    For Ctr& = LBound(ArrayInsen) To UBound(ArrayInsen)
        .Replace What:=ArrayInsen(Ctr&, 1), Replacement:=ArrayInsen(Ctr&, 2), LookAt:=xlPart, _
            SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
    Next Ctr&
    Application.DisplayAlerts = True
End With
Application.ScreenUpdating = True
End Sub

Appreciating any assistance with this issue.

Upvotes: 2

Views: 181

Answers (1)

Dunn
Dunn

Reputation: 515

Could you not make this entire thing work through an array with if statements instead of the "Find/Replace" function?

For example, if arr1 is a 2D array with your find values in arr1(i,1) and replacements in arr1(i,2), you could quickly cycle through just by comparing to the values in your range, by putting the range into arr2.

sub replacement()

dim arr1(), arr2() as variant
dim i,j,k,m as long

arr1=Range("A2:B")
arr2=Range("...") 'the values you want to compare and replace

for i=1 to num 'dimension of the 1st array
    for j=1 to num2 'dimension of 2nd array
         for k=1 to num3 '2nd dimension of arr2
              if arr2(j,k)=arr1(i,1) then
                   arr2(j,k)=arr1(i,2)
         next
    next
next

then just post the array into whichever range you want at the end here.

Upvotes: 2

Related Questions