Case Sensitive w/ IF

J

jeffP

All I'm trying to do is check a cell for a text answer:

If ActiveCell.Text = "Yes" Then

but I need to test for YES,YEs,Yes,and yes

Do I really need to nest four If statements to do that?

Thanks for any and all help
 
I

Ian

If Ucase(ActiveCell.Text) = "YES" Then

This checks whether the text in the active cell matches, when changed to
upper case.
 
D

Dave Peterson

One way:
if lcase(activecell.text)="yes" then

Another:
if strcomp(activecell.text, "yes", vbtextcompare) = 0 then

or put
Option Compare Text
at the top of the module
 
R

Ron Rosenfeld

All I'm trying to do is check a cell for a text answer:

If ActiveCell.Text = "Yes" Then

but I need to test for YES,YEs,Yes,and yes

Do I really need to nest four If statements to do that?

Thanks for any and all help
Click to expand...

Do something like:

If Ucase(ActiveCell.Text)= "Yes" Then


--ron
 
B

Bernard Liengme

I experimented with

Sub what()
If UCase(ActiveCell.Text) = "YES" Then
Range("D1") = "OK"
Else
Range("D1") = "No"
End If
End Sub

and found <If UCase(ActiveCell.Text) = "YES"....> does what you want
 
J

jeffP

Thanks to all for the quick and good answers.
Always interested in learing so Dave could you explain further the Option
Compare Text?

thanks again
 
D

Dave Peterson

You can tell excel to ignore case in text comparisons for that module.

Just put that "option compare text" at the top of the module.

Put it at the top of a test module.
select Option and hit F1. You'll see VBA's help for all the Option options.
 
D

Dave Peterson

Just a typo alert...

I bet Ron meant YES in:
If Ucase(ActiveCell.Text)= "Yes" Then
 
J

jeffP

Dave,
I tried this originally 'cause it seemed so simple for my small routine BUT
I put it inside the procedure and of course got error ....
the strcomp works just fine also

Thanks as always
 
D

Dave Peterson

I'm kind of confused. Does this mean that after you moved that
"Option compare text" to the very top of the module that you got your code to
work ok (with no change)?


Dave,
I tried this originally 'cause it seemed so simple for my small routine BUT
I put it inside the procedure and of course got error ....
the strcomp works just fine also

Thanks as always
Click to expand...
 
Top