Find and Replace showing an error that it used to not have.

[hickmanb9]

This macro used to work perfectly fine.

We use it to replace about 1000 different items in a column on an inventory spreadsheet from a supplier.

Unfortunately, it now returns this error message...

We have a separate file that we keep this chart in.

Below is the code we used for it. Can anyone let me know the issue with the code or a better solution?

Sub ReplaceMulValues()
Dim myRange As Range, myList As Range
Set myRange = Application.Selection
Set myRange = Application.InputBox("Select one range to be searched", "Find And Replace Multiple Values", myRange.Address, Type:=8)
Set myList = Application.InputBox("select two column range where find/replace pairs are:", "Find And Replace Multiple Values", Type:=8)
For Each cel In myList.Columns(1).Cells
myRange.Replace what:=cel.Value, replacement:=cel.Offset(0, 1).Value (This is the highlighted line when error comes in. The error states type mismatch error)
Next
End Sub

Typically we copy these to columns into the CSV file as a new sheet. Run the macro. Select column L when prompted and the proceed to select sheet one and then column A & B for the find and replace values.

 

[StephenCrump]

Welcome to the Forum!

My guess is that you have an error value, e.g. #N/A, in cel.Offset(0, 1)

When the code breaks:

?cel.Offset(0,1).Address in the immediate window will give you the problem location.