Overview: I am trying to run a mass substitution of a large list of terms by looping a Substitute function via VBA. Details below. and a link to a possible idea for a solution I found elsewhere is below the image here. Perhaps can help.
I have a list with two columns in my 'Job Title Append' sheet. In this sheet, Column B is 'Old Text' Column C is 'New Text' Image Below.
In a separate 'Processed Compilation Tab' Sheet i have a set of data that I want to search and replace any matches from 'Old Text' with 'New Text' in the same row.
I have 300 and counting rows in the Old Text>New Text list so using VBA is only option as formula requires too many nesting.
Also, some of the Old Text>New Text substitutions will rely on case sensitive and partial string replacements so it seems doing Substitute function in VBA is the best choice.
Also if possible I would like to choose which columns in the 'Processed Compilation Tab' sheet that the Substitute vba runs on in the Data Sheet. (So that I can choose for it not to affect certain columns basically.)
___________________________________________________________________________________________________________________
I actually found this thread on a forum that seems exactly what I am trying to do but I am not sure if it does exactly what I have in mind or how to customize it to what I need. (Code provided as solution in this thread is way down at the bottom.)
https://www.ozgrid.com/forum/forum/...iple-substitute-or-replace-text-using-a-table
I have a list with two columns in my 'Job Title Append' sheet. In this sheet, Column B is 'Old Text' Column C is 'New Text' Image Below.
In a separate 'Processed Compilation Tab' Sheet i have a set of data that I want to search and replace any matches from 'Old Text' with 'New Text' in the same row.
I have 300 and counting rows in the Old Text>New Text list so using VBA is only option as formula requires too many nesting.
Also, some of the Old Text>New Text substitutions will rely on case sensitive and partial string replacements so it seems doing Substitute function in VBA is the best choice.
Also if possible I would like to choose which columns in the 'Processed Compilation Tab' sheet that the Substitute vba runs on in the Data Sheet. (So that I can choose for it not to affect certain columns basically.)
___________________________________________________________________________________________________________________
I actually found this thread on a forum that seems exactly what I am trying to do but I am not sure if it does exactly what I have in mind or how to customize it to what I need. (Code provided as solution in this thread is way down at the bottom.)
https://www.ozgrid.com/forum/forum/...iple-substitute-or-replace-text-using-a-table
