VBA For "Not Like"

[The Brown Growler]

Hi,

Could anone please help me with the VBA code for a "Not Like" comparison ?

I get an error when I try to use the "Not Like" comparison

 If Range("A" & i).Value Not Like "CLOS*" Then

If I use <> "CLOSED" And <> "CLOSE" And <> "CLOSING" then it all runs OK, however I would prefer to get something similar to a "Not Like" operator to work with a wildcard such as *

Thx & Rgds
Growlos

 

[georgedwilkinson]

You can use Left() or Mid() (or even Right()) along with the equivalency operators (=, <>).

So:

If Left(Range("A" & i).Value,4) <> "CLOS" Then

 

[Banana]

It should be noted that VBA doesn't really support wildcard or regular expression; only in the context of queries can you have either.

Also, for more flexibility, consider using InStr() which will find any pattern (without wildcard) within a string:

?InStr("This office closes at 4 o'clock.", "clos")
13

You can test if the result is greater than zero which indicates no match was found.

 

[ajetrumpet]

you can also note this stuff:

*NOT LIKE
*<>
*NOT LIKE "*"* & VALUE & "*"
*AND NOT (for double conditional statement)

 

[The Brown Growler]

Thank you for the replies, I will be able to get it to work now.


Rgds
Growlos