Testing for user entered errors in cells

[dunk123]

This used to work fine in Excel 2003, but I've just noticed that using Excel 2007 it doesn't seem to be performing in the same way. I have a range of cells in a column, and this code loops through the range to test if the user has left it blank or put in some data that is deemed to be incorrect from the list contained within the IF statement below, and if it does meet the criteria then the cell is coloured yellow and it moves on the the next one. What it is now doing is finding that every cell meets the criteria and colouring it yellow. For example, I've just witnessed sCellCheck = "BENNI X" or "TINE MUNK" and the cell has been coloured. Anyone able to advise why it would do that? Thanks all.

For Each sCellCheck In sRange
        If sCellCheck Like "" Or sCellCheck Like "*?*" Or LCase(sCellCheck) Like "x x" Or sCellCheck Like "- -" Or sCellCheck Like "-- --" Or sCellCheck Like "*--*" Or sCellCheck Like "- --" Or sCellCheck Like "- - -" Or sCellCheck Like "- - --" Or sCellCheck Like "-- -- --" Or sCellCheck Like "-- - --" Or sCellCheck Like "-- ---" Then
            sCellCheck.Select
            With Selection.Interior
                .Color = 65535
            End With
        End If
    Next

 

[prajul89]

sCellCheck Like "*?*"

I think this line is a problem it will include every string more then 3 I guess
</pre>

 

[dunk123]

Thanks. I just removed the * from that and you are right. How can I then test for a question mark within the cell anywhere, which is what that section was trying to do?

 

[prajul89]

Remember Even '?' represents any single letter string. If you want to know wheather a cell has ? then use '~?'.

 

[dunk123]

I tried:

If sCellCheck Like "" Or sCellCheck Like "~?"

but this doesn't pick it up when the value in the cell is "LOTTE .?" or "GABI O?TT"

 

[prajul89]

you forgot to put *


Put it like this:

"*~?*"
I hope it works...........

 

[dunk123]

So now we have

If sCellCheck Like "" Or sCellCheck Like "*~?*"

and it does not pick up "JEPPE HIRSCH?" but does pick up "LEIF OLSEN~?"

Should I perhaps be using the character USCII number instead to test for a question mark? How would this be done to include the wildcards?

 

[prajul89]

My Bad, I got a bit confused between excel and VBA my above code is useful for Excel and for VBA this would help:

"*[?]*"

 

[dunk123]

Awesome, that does it. Thanks so much for your help.