Find and Replace Entire Array on one cycle

[mrmmickle1]

Right now I am cycling through each element in 2 arrays that are about 200 elements each. Is there a way where I can do this all in one action rather than the way I am currently doing it?

[COLOR=#0000ff]Sub[/COLOR] FindandReplace()
   
   [COLOR=#0000ff] Dim[/COLOR] vFindText    [COLOR=#0000ff] As Variant[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] vReplaceText  [COLOR=#0000ff]As Variant[/COLOR]
[COLOR=#0000ff]    Dim[/COLOR] rowStart     [COLOR=#0000ff] As Integer[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] rowEnd      [COLOR=#0000ff]  As Long[/COLOR]
  [COLOR=#0000ff]  Dim[/COLOR] ArrEle       [COLOR=#0000ff] As Long[/COLOR]
   
[COLOR=#000000]    rowStart = 2[/COLOR]
    rowEnd = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
   
    vFindText = Range(Cells(rowStart, 1), Cells(rowEnd, 1))
    vReplaceText = Range(Cells(rowStart, 2), Cells(rowEnd, 2))
   
    [COLOR=#0000ff]For[/COLOR] ArrEle = 1 [COLOR=#0000ff]To[/COLOR] rowEnd - 2
        Columns("H:H").Replace What:=vFindText(ArrEle, 1), Replacement:=vReplaceText(ArrEle, 1)
    [COLOR=#0000ff]Next[/COLOR] ArrEle
 
[COLOR=#0000ff]End Sub[/COLOR]

Any help would be much appreciated.

 

[JLGWhiz]

From what I see, you are only making one pass down column A against column H. If any of the items in column A hit in column H, then it will replace that item with the corresponding item in column B. Pretty straight forward.

 

[mrmmickle1]

JLGWhiz,

So your saying this is as optimized/efficient as its going to get?

Is there a possibility that a "modified" Vlookup would be quicker? Or is this the way to go?

The find and replace really only applies to between 2 to 20 values in column H

 

[JLGWhiz]

JLGWhiz,

So your saying this is as optimized/efficient as its going to get?

Is there a possibility that a "modified" Vlookup would be quicker? Or is this the way to go?

The find and replace really only applies to between 2 to 20 values in column H

I am not a professional programmer, and still have much to learn about VBA, but at the moment I cannot think of a quicker method of replacing the values than the one you are using. You might get a little more speed if you put the search items and replacement items into an array and loop through the array rather than a range. But I doubt that the time difference would be significant unless you have a couple hundred thousand items to check. Everything is relative.

 

[mrmmickle1]

Thanks for the follow up. I appreciate your insight.

 

[JLGWhiz]

Thanks for the follow up. I appreciate your insight.

You're welcome,
Regards, JLG