vba code : find exact phrase in Column C using Column M and replace with Column N

The total list of Model Names to change is 193.
The macro works except it doesnt work when you have example:
"Silverado 1500" "Silverado 1500 Series" "Silverado 1500 HD" etc....these should all be changed to: "Silverado" but that's where the problem is.

Ok, I can try these ideas
I was hoping there was some additional code that would fix it without changing order.

kvsrinivasamurthy - your code did not work.

TMS - when i sorted the data z to a it worked better but still doesn't work

Still having issue with the "Truck Model Names" and a few others.

I've attached the full list of names to change.

I'm thinking the model names change as it goes down the list....but then a later change causes a conflict with a row which has already been changed.
Also, each model name change ends up changing thousands of rows of data in column C....and also many times it is 2 or more times in each row/cell of column c.

As per example Post #1 attachment, you could try this

Please Login or Register  to view this content.

jolivanes....you're the man!
so far everything i'm checking looks good.

if i want to run this same thing on column B (2)
what do i change in your code?

thanks again.

Please Login or Register  to view this content.

should work for the same setup.

Don't know why the other suggestions don't work as you mentioned because a find and replace like this, which is just about the same, works.
Try it on your actual workbook, or a copy would be better, and let us know.

Please Login or Register  to view this content.

You're right Trevor. My mistake. I did not check it close enough. It leaves part of the name.

hi jolivanes,

i tried changing the one line in your code as you described, changing the "3" to "2".
it did not work.
is there something else in the code that needs to change?

here is what the data is like in column 2 (b):

<B> Applications: GMC Sierra 1500 Series 2007-2013; GMC Yukon 2007-2014; GMC Yukon XL 2007-2014; GMC Sierra Denali 2007-2010; GMC Sierra Denali 1500 2011-2013; GMC Savana 1500 2010-2013</B>

Attach a representative workbook.

I thought maybe it was a format issue in column 2, but still didn't work.
The sheet is the same, column 2 has data like this:

<B> Applications: GMC Sierra 1500 Series 2007-2013; GMC Yukon 2007-2014; GMC Yukon XL 2007-2014; GMC Sierra Denali 2007-2010; GMC Sierra Denali 1500 2011-2013; GMC Savana 1500 2010-2013</B>

When the macro runs for column 3, it takes about 15 seconds to complete and works properly.

When I change the one line to 2, it doesnt go into that processing mode and doesnt do anything.

Thanks again.

Hello jolivanes,
";" didn't work.
do you think it has something to do with the <B> and </B> ?

thanks again

Attach a workbook with the original data in it.
If there is a lot of data in it, delete some so the size of the workbook is reasonable.

row 2 contents:

<B> Applications: GMC Sierra 2500 Series 2002-2002; GMC Sierra C3 2001-2003; GMC Sierra Denali 2004-2006; GMC Yukon 2001-2006; GMC Yukon XL 2001-2006; GMC Yukon XL Denali 2004-2006</B> <BR>Size: 17x7.5 <BR>Lugs: 6 <BR>Bolt Pattern: 5.5in <BR>

There is data both in Column B and C but no replacement values anywhere.

the replacements are in M and N but much higher in the sheet as it's only 193 rows of data.
i'll send another with some sample data in M and N

attached the sample again with data in m and n

Have you tried using it on Column 3 (=Column C)?
The data in Column C is obscuring the data in Column B so even if it did change the data in Column B, you would not see it because of that.
Or clear all data in Column C and see if your code works on Column B

It's not that.
For some reason it just wont run on column b (2).
I've changed the 3's to 2's
Also tried changing the | to ;

Both "<B> Applications: " at the beginning and "</B> <BR>Size: 17x7.5 <BR>Lugs: 6 <BR>Bolt Pattern: 5.5in <BR>" ought to be removed.
You can do that with code or manually.
There is no "Yukon XL Denali" in Row B3. There is "GMC Yukon XL Denali 2004-2006".
BTW. Do you have to pay for each line published? Just one little line is a bit cheap isn't it.

Dang, it's always something isn't it.

Thanks for taking a run at it.

Well, what do you want to do?
Can we delete the parts that need deleting?
We should do it in an understandable way so you can change it for future similar problems.

I can't permanently delete the <b> and <br> stuff.
If there is a script that temporarily deletes it or ignores it to properly change the Model Names that would be ok .
As long as they are back after running it.

jolivanes,

it doesn't need to be 1 macro to do both column B (2) and C (3) at the same time.

you're code above in response #13 works for column C(3)

columns M(13) and N(14) remain the same.

just somehow need to make the exact same Model Name changes to column B(2) as what we do to column C(3)

Is "GMC Yukon XL Denali 2004-2006" the same for changing purpose as "Yukon XL Denali"?

The to be replaced values and the replacement values are in Columns M and N starting at M1.
The data to be checked is in Column B starting at B3.
I suggest to try on a copy of your original workbook first.
Don't know if it does what needs to happen because an attachment with just one row is pretty skimpy.

Please Login or Register  to view this content.

For future threads.
Attach a representative workbook with between 10 and 20 examples, not just 1.

Hi jolivanes,

getting a "runtime error "S" invalid procedure call or argument"

dbug is highlighting this: dd = Mid(c, InStr(Len(d), c, "<"))

Attach a representative workbook with at least 20 rows of data and multiple rows for the data to look for and to change into (Columns M and N)
If there are different strings at the beginning and at the end, make sure that shows. If they are the same on all, also let us know.

See attached