VBA if value in column A then replace in column B

[ghostexcel]

Hi
Im currently using this VBA code that looks at values in column A in Sheet2 and if found it removes this value in column A in Sheet1.

Please Login or Register  to view this content.

So, on Sheet1 in column A I have:

a white

b black

d white

c orange

On Sheet2 in column A I have:

a

b

d

c

When I run the VBA, as a result I have White, Black, Orange, White, without a, b, c, d, as these values are in Sheet2.

But if I have these in Sheet1 in column A:

white a

black b

white d

orange c

the code does not work properly.

Cheers

[6StringJazzer]

It worked fine for me. I'll bet that your real file is not using the same data that you are showing in your post.

[walruseggman]

Agreed with 6StringJazzer, those combos work.

Where you will run into issues is things like c black >> clack or banana d >> banand

This is because your values in each row are just strings. You mentioned yesterday you only wanted to replace "exact matches", but a concept like this doesn't exist natively in VBA inside a string; i.e. VBA doesn't understand the concept of "whole words" within a string. At least I am not aware that it does.

Just off the top of my head you'd probably have to ditch the find and replace and maybe instead use range.Find, then check using ASCII values to see what's before and after each string. But this sounds like a headache.

Maybe someone knows a better way with regular expressions or something.

[6StringJazzer]

Where you will run into issues is things like c black >> clack or banana d >> banand

This is very perceptive. It might be that a more sophisticated solution such as regular expressions would be the right fit. However, ghostexcel, we need much more detail about these strings you are working with. I suspect that the example in your first post is a toy example and that is oversimplifies your actual problem.

If you can give more detail and a truly rigorous specification for what strings you want to find and remove then we can look at a more comprehensive solution, whether it uses regular expressions, Like, or something else.