Jan 25 2022 04:33 AM
Jan 25 2022 04:33 AM
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:
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
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