VBA to find and replace arrays in specific columns

Hello,

I have two arrays. One is the "Find" array, the other is the "replace" array. They are replacing values in specific columns on another sheet in the same workbook. Both these arrays are driven by formulas. I have a super long program written that goes through and replaces each value in each specific column, but it keeps failing. Can anyone help with the code for this? I have a hunch my original code would be useless, so I haven't included it. I've posted the table with the find and replace arrays, and the specific columns they will be finding and replacing on in the other sheet.

find_replace.JPG

Thank you in advance.

I assume your find/replace list is not on the same sheet as the one where the values are replaced?

Give this a shot:

Sub test()

Dim wsr As Worksheet
Dim wss As Worksheet
Dim i As Long, r As Long

Application.ScreenUpdating = False
'assumes replace list is columns F, G, H and that they are arranged as per the example sheet you provided.. Column, Original Value, Replacement Value
'change sheet names below as required.
Set wss = ThisWorkbook.Worksheets("Sheet1") 'the source of the find/replace list
Set wsr = ThisWorkbook.Worksheets("Sheet2") 'the sheet where replacing should take place
lr = wss.Range("F" & Rows.Count).End(xlUp).Row 'last row with data in the replacement table, based on the "Column" column.

For i = 2 To lr
    wsr.Columns(wss.Range("F" & i).Value).Replace what:=wss.Range("G" & i).Value, replacement:=wss.Range("H" & i).Value, lookat:=xlWhole 'change to xlPart if only part of the cells is being replaced
Next i
Application.ScreenUpdating = True

End Sub

How exactly you implement that will depend on whether it is all sheets (except the one with find/replace list) or just a few. But yes, in essence it would be a for ... each loop. If you give me the details I can add that to the code, unless you are up to the task yourself of course

My pleasure

If you need help don't hesitate to ask. If you solve it on your own, please come back and mark the thread as solved so we know no follow-up is required. Thanks!

Please use code tags when posting code (highlight what is code, and click the # icon)

Do you want to loop through ALL sheets in the workbook? Or you want to just do the replace in 2 sheets out of many? "For Each wsSheet In Worksheets" loops through them all.

Just to confirm... you have 2 sheets that contain "find and replace" lists? Or you want to replace values in 2 sheets based on just one list? (ideally tell me what each of the 3 names is in terms of "to replace" or "list")

If you have 2 sheets with find and replace values, are their columns set up the same?

I haven't posted loop code just because how to structure it depends a bit on my questions above. Let me know, and I'll post updated code