VBA Bulk Find and Replace for Long Strings

[lneidorf]

Hi there.

I've got some code that I've been using for quite some time for bulk find and replace operations. I've got a tab with the values to find in col A and the values to replace with in col B. This macro looks through a range on another tab and performs find and replace operations en masse.

Here's the code:

Sub MultiFindNReplace()
    Dim myList, myRange

    Set myList = Sheets("Sheet1").Range("A1:B211") 'two column range where find/replace pairs are
    Set myRange = Sheets("Record List").Range("B2:B1550") 'range to be searched
    For Each cel In myList.Columns(1).Cells
        myRange.Replace what:=cel.Value, Replacement:=cel.Offset(0, 1).Value
    Next cel
End Sub

When I run this with my current sets of values, I'm getting Run-time 13 Type Mismatch errors.

I believe the issues is that the values I'm attempting to find / replace now exceed 255 characters. These values are long folder paths.

Any thoughts?

Thank you!

 

[tonyyy]

You might consider .Substitute rather than .Replace...

Sub MultiFindNReplace2()
    Application.ScreenUpdating = False
    Dim myList As Range, myRange As Range, cel As Range, r As Range
    Set myList = Sheets("Sheet1").Range("A1:B211") 'two column range where find/replace pairs are
    Set myRange = Sheets("Record List").Range("B2:B" & Sheets("Record List").Cells(Rows.Count, "B").End(xlUp).Row) 'range to be searched
    For Each cel In myList.Columns(1).Cells
        For Each r In myRange
            r.Value = WorksheetFunction.Substitute(r.Value, cel.Value, cel.Offset(0, 1).Value)
        Next r
    Next cel
    Application.ScreenUpdating = True
End Sub

Cheers,

tonyyy

 

[lneidorf]

Thanks Tonyyy! That did the trick!

 

[tonyyy]

You're welcome. Glad it worked out...