Case Sensitive w/ IF

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

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

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

--
Ian
--
"jeffP" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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


jeffP wrote:
>
> 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

--

Dave Peterson

On Sun, 5 Feb 2006 10:26:28 -0800, "jeffP" <[email protected]>
wrote:

>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

Do something like:

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


--ron

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
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"jeffP" <[email protected]> wrote in message
news:[email protected]...
> 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
>

Hi!

Try this:

=OR(EXACT(A1,{"YES","YEs","Yes","yes"}))

Biff

"jeffP" <[email protected]> wrote in message
news:[email protected]...
> 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
>

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

"Dave Peterson" wrote:

> 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
>
>
> jeffP wrote:
> >
> > 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
>
> --
>
> 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.

jeffP wrote:
>
> 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
>
> "Dave Peterson" wrote:
>
> > 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
> >
> >
> > jeffP wrote:
> > >
> > > 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
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Just a typo alert...

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



Ron Rosenfeld wrote:
>
> On Sun, 5 Feb 2006 10:26:28 -0800, "jeffP" <[email protected]>
> wrote:
>
> >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
>
> Do something like:
>
> If Ucase(ActiveCell.Text)= "Yes" Then
>
> --ron

--

Dave Peterson

On Sun, 05 Feb 2006 14:06:10 -0600, Dave Peterson <[email protected]>
wrote:

>Just a typo alert...
>
>I bet Ron meant YES in:
>If Ucase(ActiveCell.Text)= "Yes" Then
>
>

"Typos 'R Us"!!
Thanks



--ron

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


"Dave Peterson" wrote:

> 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.
>
> jeffP wrote:
> >
> > 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
> >
> > "Dave Peterson" wrote:
> >
> > > 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
> > >
> > >
> > > jeffP wrote:
> > > >
> > > > 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
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> 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)?



jeffP wrote:
>
> 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
>
> "Dave Peterson" wrote:
>
> > 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.
> >
> > jeffP wrote:
> > >
> > > 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
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > 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
> > > >
> > > >
> > > > jeffP wrote:
> > > > >
> > > > > 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
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson