Find and Replace Loop Using Spreadsheet

[HoboEconomist]

Hi all,

Sorry for the beginner VBA question - I've been stuck for a while and haven't been able to adapt any answers from this forum to quite get the results I would like.

I have a range (B46:C54) on multiple sheets that contains values that need to be used in a find and replace. B46:B54 contains the "find" values, while C46:C54 contains the "replace" value. I have text strings in cells F71:F80 where I want the find and replace to occur.

I had previously set this up using the following code:

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Integer
Dim rplcList As Integer
Dim tbl As ListObject
Dim myArray As Variant

'Create variable to point to table
  Set tbl = ActiveSheet.ListObjects("Email_Controls")

'Create an Array out of the Table's Data
  Set TempArray = tbl.DataBodyRange
  myArray = Application.Transpose(TempArray)
 
'Designate Columns for Find/Replace data
  fndList = 1
  rplcList = 2

'Copy Text from Email Body to Email Output section
  ActiveSheet.Range("F46:F55").Copy
  ActiveSheet.Range("F71:F80").PasteSpecial Paste:=xlPasteValues

'Loop through each item in Array lists and conduct find/replace
  For x = LBound(myArray, 1) To UBound(myArray, 2)
    ActiveSheet.Range("F71:F80").Replace What:=myArray(fndList, x), Replacement:=myArray(rplcList, x), _
            LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
            SearchFormat:=False, ReplaceFormat:=False
  Next x

End Sub

While this works just fine, it turns out that one of the add-ins I need to use deletes all defined tables after running (I am very sad about this). So ideally, I need a piece of code that does the same thing, but doesn't use tables to do it. It would be great if someone could point me in the right direction here!

 

[Fluff]

Will it always be B46:C54 everytime (with no blank cells)?

 

[HoboEconomist]

Thanks for the speedy response!

Yes, it will be the same range every time. I had previously left the last few rows in the range blank so that a user could add their own "find" and "replace" fields as needed. If this creates too much extra hassle, I can do without that feature

 

[Fluff]

Ok, you can do it like

Sub HoboEconomist()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Range("B46:C" & Range("B" & Rows.Count).End(xlUp).Row).Value2
   For i = 1 To UBound(Ary)
      Range("F71:F80").Replace Ary(i, 1), Ary(i, 2), xlPart, , False, , False, False
   Next i
End Sub

 

[HoboEconomist]

Thank you Fluff! This works like a charm.

Just so I make sure I'm understanding (and so I don't bother you fine folks again), does the line "Ary = Range ..." set the "last row" for the search to be whatever is the last cell with an entry in column B?

The For loop then "finds" row i, column B and "replaces" with row i column C?

Thanks again!

 

[Fluff]

This part

Range("B" & Rows.Count).End(xlUp).Row

find the last row in col B & in conjunction with the rest of that line populates a 2d array with the values from cols B&C.
The loop the runs through the array & does the replacement, where ary(i,1) is the first col in the array & Ary(i,2) is the second col.

 

[HoboEconomist]

Understood. Today I learned! Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.