Forum Discussion
Trying to use VBA to perform an Automated find and replace function
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.
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.
- The range is no longer valid because I am now using the sample doc and not original.
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