Lookup Table to Find/Replace Every Instance of a String (2/2)

Hi Friends,

I am new to vba.

I have a lookup table that contains main strings and their relevant replacement strings. say for example
Col A Col B
Old string1 New string1
Old string2 New string2 .....n

My problem is, i want to replace multiple instances of original old string to be replaced with new replacement string (provided in a lookup table) in another tab, which has multiple columns

My ColA old strings actually are substrings in another tab. e.g. seven wondersOld string1greatwallofchina
The output should be like: seven wondersNew string1greatwallofchina

I even tried the code, that was provided at

http://www.excelforum.com/excel-gene...-a-string.html

But some sort of runtime error pops up. I just can't figure out what's wrong in the code. My code is

My code:

Please Login or Register  to view this content.

Here is the sample file, on which i tried that code. Actual file of me spans >50 columns and >5000 rows.

It might be a problem with the length of the strings that you are attempting to use. Your code works if there are 255 characters or less in the first string.

An alternative would be to do it in several steps by replacing segments of the search string with a set of unusual characters and then sequentially replace these with your final string.

Thanks Martin,

Yes, you are right. code works if there are 255 characters or less in the strings. At least i can solve 50% of my problem now, earlier i thought code is not working at all.
Now it has become altogether a new problem.
Isn't there any other way (any variable, array or other sort of thing) to handle a string of ~500 characters. Because in my current sheet every string to be replaced (around 5000 entries) is unique and there occurrence individually is barely more than 10 collectively in all columns. Giving them (long strings) a set of unusual characters, would require to generate another unique lookup table.
Some of my new strings are also more than 500 characters long. In this case as well, i may need to generate new lookup. so probably solution doesn't seem much viable.




Can you plz show me

Thanks Martin,

Yes, you are right. code works if there are 255 characters or less in the strings. At least i can solve 50% of my problem now, earlier i thought code is not working at all.

Now it has become altogether a new problem.
Isn't there any other way (any variable, array or other sort of thing) to handle a string of ~500 characters.
Because in my original sheet every string to be replaced (around 5000 entries) is unique and there occurrence individually is barely more than 10 collectively in all columns. Giving them (long strings) a set of unusual characters, would require to generate another unique lookup table.
Some of my new strings are also more than 500 characters long. In this case as well, i may need to generate new lookup. so probably solution doesn't seem much viable.

If possible, can u manage to provide a dynamic solution to handle a long string (>255 characters). I don't have a good hand in vba and new to it.

Thanks 4 testing my code.

See if this works for you

Please Login or Register  to view this content.

Thanks Jindon,

I tried your code as well, definitely it works in my given test file, but got errors in my main file (got stuck in infinite loop). so didn't gave 2nd thought to it and tweaked my own code.

Thanks for your prompt response and taking time to write code for me.
You guys really rocks !