Jan 25 2022 04:33 AM
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.
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).
The code is the following:
Sub multiFindandReplace()
Dim myList
Dim myRange
Set myList = Sheets(“Click USER Ref”).Range(“A2:B24”)
Set myRange = Sheets(“Daily Pick Data”).Range(“D5:D515”)
For Each cel In myList.Columns(1).Cells
myRange.Replace What:=cel.Value, Replacement:=cel.Offset(0, 1).Value, LookAt:=xlWhole
Next cel
End Sub
Thank you.
Jan 25 2022 04:43 AM
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.
Jan 25 2022 04:58 AM
For some reason I can Not upload a file or at least do not see an option to upload a file.
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".
Sorry for not being able to post the upload the file.
Jan 25 2022 05:06 AM
Jan 25 2022 10:55 AM
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.
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" & 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
Jan 25 2022 11:26 AM