1

I receive a quarterly data set where I need to make some general changes to the data. The changes are routinely the same and I want to use a macro to change them in one fell swoop rather than find/replace manually. I found a script that saves my data as an array and then searches the entire workbook. It works BUT I only want it to find/replace in a specific range. I've tried many times to alter the code, but I am unable to. I don't even know if this is the best way to accomplish it - very open to all feedback!

Sub Multi_FindReplace()

Dim sht As Worksheet
Dim fndList As Variant
Dim rplcList As Variant
Dim x As Long

fndList = Array("Unnamed Rd", "526-566 Stanford UniversityLagunita Dr", "Social Science Parking Structure")
rplcList = Array("Departure Access Rd", "526-566 Lagunita Dr", "4139 Campus Dr")

  For x = LBound(fndList) To UBound(fndList)
  For Each sht In ActiveWorkbook.Worksheets
    sht.Cells.Replace What:=fndList(x), Replacement:=rplcList(x), _
      LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
      SearchFormat:=False, ReplaceFormat:=False
  Next sht

  Next x

End Sub
tlaw the outlaw
  • 95
  • 2
  • 10
  • 5
    change `Cells` to the desired range something like `Range("A1:B10")` – Scott Craner Dec 22 '16 at 21:04
  • Yes, I agree with @ScottCraner. "Cell" will perform this on the entire sheet. You need to specify the range you want to perform this on just like the example Scott provided. You could make it slightly dynamic and change `sht.Cells` to something like `Selection` that way you can just select what you want then run your macro. – Kerry White Dec 23 '16 at 03:49
  • @CDay ask your own question with the code and an explanation of the error and on which line it appears. Link back to this question as reference. – Scott Craner Dec 11 '20 at 15:19

0 Answers0