Find / Replace Iterative Macro

[shantishunn]

I'm working to replace scattered values is one tab with values mapped in the second tab. The manual process for this is as follows:

select tab 2
select value in column A, row 2
copy value
ctrl+F and paste value into "Find what:" field
expand options and make sure 'Match Case' and 'Match entire cell contents' is selected
go back to tab 2 and select value from column F, row 2
copy value
paste value into the "Replace with:" field
select tab 1
click "Replace All" button
Proceed to row 3 on tab 2 ...and so on and so forth.

I tried to record a macro, but could not get it to iterate down every row with content performing the setting of the Find Replace values and then taking the "Replace All" action on Tab 1.

Any ideas on how to do this / set this up?

Thanks in advance!

Shanti

 

[Rick Rothstein]

The manual process for this is as follows:

select tab 2
select value in column A, row 2
copy value
ctrl+F and paste value into "Find what:" field
expand options and make sure 'Match Case' and 'Match entire cell contents' is selected
go back to tab 2 and select value from column F, row 2
copy value
paste value into the "Replace with:" field
select tab 1

What cells on "tab 1" are selected for the "Replace All" to work on?

click "Replace All" button
Proceed to row 3 on tab 2 ...and so on and so forth.

1) What is the answer to the above question (highlighted in color) at the indicated location within your manual procedure?

2) Are "tab 1" and "tab 2" the actual name of your sheets or simply indicators for the first and second worksheets (no matter what their names are)?

 

[shantishunn]

Rick,

Thanks for the reply. Working with a buddy of mine, we came up with a way to do it with VLOOKUP. This was our formula that we essentially built a 'ghost' array within Tab 1 (INDEX ONLY):: =IFERROR(VLOOKUP('INDEX ONLY'!F2,'Index Cat DeDupe'!$A$1:$F$2162,5,FALSE),'INDEX ONLY'!F2)

Essentially, this is a project where I am mapping category index values for a B2B catalog to a new eCommerce taxonomy for the company's B2B website. The catalog index categories plentiful and by returning the raw value from the source cell for the initial lookup as the error state. This then allows me to immediately see if / when there are catalog index values that are unmapped and map them appropriately. The fun that comes out of consulting when SEO combines with website architecture and taxonomy.

Long story short, we figured out one way to do it (I'm sure there are others as we did experiment with INDEX too).

However, I can definitely say I'm glad I found this forum and I want to thank you for such a prompt response Rick.

Shanti

 

[Rick Rothstein]

Rick,

Thanks for the reply. Working with a buddy of mine, we came up with a way to do it with VLOOKUP. This was our formula that we essentially built a 'ghost' array within Tab 1 (INDEX ONLY):: =IFERROR(VLOOKUP('INDEX ONLY'!F2,'Index Cat DeDupe'!$A$1:$F$2162,5,FALSE),'INDEX ONLY'!F2)

Long story short, we figured out one way to do it (I'm sure there are others as we did experiment with INDEX too).

Actually, instead of formulas, I was going to give you a macro solution (less overhead that way there would be no need to maintain a large amount of formulas). I guessed at the answers to my questions from what you last posted... those items are highlighted in blue in the code below (so if you want to try the macro out on a copy of your original data to see how it works, you can change the assignments where I guessed wrong).

Sub DoReplacements()
  Dim R As Long, LastTab2Row As Long, Tab1ColumnsForReplacements As String, Tab1 As Worksheet, Tab2 As Worksheet
[COLOR=#0000ff][B]  Tab1ColumnsForReplacements = "A:F"
  Set Tab1 = Sheets("INDEX ONLY")
  Set Tab2 = Sheets("Index Cat DeDupe")
[/B][/COLOR]  LastTab2Row = Tab2.Cells(Rows.Count, "A").End(xlUp).Row
  Application.ScreenUpdating = False
  For R = 2 To LastTab2Row
    Tab1.Range(Tab1ColumnsForReplacements).Replace Tab2.Cells(R, "A").Value, Tab2.Cells(R, "F").Value, xlWhole, , True
  Next
  Application.ScreenUpdating = True
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (DoReplacements) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.