Trying to use VBA to perform an Automated find and replace function

Question

Hello,I am having trouble performing a find and replace with VBA. I am using a piece of code I modified for my work book. However I am running into a "compile error Expected list separator or )" . I do not see the issue at this time and I have been trying to research the root causes behind the message but I do not see in the code why it would cause it at this time. I am using Office 365.&nbsp;The purpose of my code is to look at Daily Pick Data worksheet with a column that has some old user codes in it and replace them using a table on a Click USER Ref worksheet that has the new user code.I am trying to automate this process because I will be pulling this data daily and do not want to find and replace manually or create extra columns just to use the lookup fx(s).&nbsp;The code is the following:&nbsp;Sub multiFindandReplace()Dim myListDim myRangeSet myList = Sheets(“Click USER Ref”).Range(“A2:B24”)Set myRange = Sheets(“Daily Pick Data”).Range(“D5:D515”)For Each cel In myList.Columns(1).CellsmyRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWholeNext celEnd Sub&nbsp;Thank you.&nbsp;

subodh_tiwari_sktneer · Answer

Ishmale&nbsp;How about uploading a sample file with some dummy data in both the worksheets along with the logic you are trying to replace data. That would help to suggest a working solution considering your existing data.

ishmale · Answer

Subodh_Tiwari_sktneer&nbsp;For some reason I can Not upload a file or at least do not see an option to upload a file.&nbsp;I would like to search the user code column in the "Daily Pick Data" sheet and then using the list in the "Click USER Ref" sheet find the old code and replace the "Daily Pick Data" cell with the new user code from the table on "Click USER Ref".&nbsp;&nbsp;Sorry for not being able to post the upload the file.&nbsp;&nbsp;&nbsp;

ishmale · Answer

&nbsp;

ishmale · Answer

The range is no longer valid because I am now using the sample doc and not original.

subodh_tiwari_sktneer · Answer

Ishmale&nbsp;&nbsp;Please give this a try and let me know if this works as desired.Remember to tweak the worksheets and ranges if not as per the sample data you provided.&nbsp;Sub ReplaceOldUserCodeWithNewUserCode()
Dim wsData      As Worksheet
Dim wsUser      As Worksheet
Dim x           As Variant
Dim dict        As Object
Dim Rng         As Range
Dim Cel         As Range
Dim lr          As Long
Dim i           As Long

Application.ScreenUpdating = False

Set wsData = Worksheets("Daily Pick Data")
Set wsUser = Worksheets("Click USER Ref")

lr = wsData.Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = wsData.Range("A2:A" &amp; lr)

x = wsUser.Range("A1").CurrentRegion.Value
Set dict = CreateObject("Scripting.Dictionary")

For i = 2 To UBound(x, 1)
    dict.Item(x(i, 1)) = x(i, 2)
Next i

For Each Cel In Rng
    If dict.exists(Cel.Value) Then
        Cel.Value = dict.Item(Cel.Value)
    End If
Next Cel
Application.ScreenUpdating = True
End Sub

Forum Discussion

Trying to use VBA to perform an Automated find and replace function

6 Replies

Resources