Noobee Case Select Question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CH - 20 Dec 2006 16:29 GMT

I have the following code below in which I want it to check the value
of two cells and if the two cells = certain values then hide rows on a
different sheet.

For example if A12 = "BLUE" AND B16 = "DOG" then hide rows on sheet
"CAR" Else dont hide the rows.

I prefer to use the case select statement but the code below doesn't
work. Why?

'Case Select
'=====================================
Select Case testcase
Case UCase(Range("A12").Value = "Blue", Range("D27") = "Dog")
Sheets("Car").Rows("1:20").EntireRow.Hidden = True
Case Else
Sheets("Car").Rows("1:20").EntireRow.Hidden = False
End Select

End Sub

Reply to this Message

CH - 20 Dec 2006 16:36 GMT

> I have the following code below in which I want it to check the value
> of two cells and if the two cells = certain values then hide rows on a
[quoted text clipped - 16 lines]
>
> End Sub

Reply to this Message

CH - 20 Dec 2006 16:39 GMT

> > I have the following code below in which I want it to check the value
> > of two cells and if the two cells = certain values then hide rows on a
[quoted text clipped - 16 lines]
> >
> > End Sub

Reply to this Message

Jim Cone - 20 Dec 2006 16:37 GMT

Change the word "testcase" to True.

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

"CH" <christopher.hubbard@gmail.com>
wrote in message
I have the following code below in which I want it to check the value
of two cells and if the two cells = certain values then hide rows on a
different sheet.
For example if A12 = "BLUE" AND B16 = "DOG" then hide rows on sheet
"CAR" Else dont hide the rows.
I prefer to use the case select statement but the code below doesn't
work. Why?

'Case Select
'=====================================
Select Case testcase
Case UCase(Range("A12").Value = "Blue", Range("D27") = "Dog")
Sheets("Car").Rows("1:20").EntireRow.Hidden = True
Case Else
Sheets("Car").Rows("1:20").EntireRow.Hidden = False
End Select
End Sub

Reply to this Message

CH - 20 Dec 2006 17:11 GMT

> Change the word "testcase" to True.
> --
[quoted text clipped - 21 lines]
> End Select
> End Sub

Thanks Jim, that worked (so it will now hide the rows) BUT for some
reason its only hiding/unhiding the rows based on the value of B16 =
"DOG". It seems like it's ignoring the value of A12. The rows will
hide only when B16 = "Dog" in stead of when A12 = "Blue" AND B16 =
"Dog".

P.S. also it didnt like my UCASE for some reason so i took it out

'Case Select
'=====================================
Select Case True
Case Range("A12").Value = "Blue", Range("B16").Value = "Dog"
Sheets("Car").Rows("1:20").EntireRow.Hidden = True
Case Else
Sheets("Car").Rows("1:20").EntireRow.Hidden = False
End Select
End Sub

Reply to this Message

Dave Peterson - 20 Dec 2006 17:00 GMT

This looks more natural as If/then/Else that select case (to me, anyway):

if UCase(Range("A12").Value) = ucase("Blue") _
and ucase(Range("D27").value) = ucase("Dog") then
Sheets("Car").Rows("1:20").EntireRow.Hidden = True
Else
Sheets("Car").Rows("1:20").EntireRow.Hidden = False
End if

Remember that if you're using ucase(), you'll want to compare uppercase strings:

ucase(anything) will never equal "Dog". It may equal "DOG", though.

> I have the following code below in which I want it to check the value
> of two cells and if the two cells = certain values then hide rows on a
[quoted text clipped - 16 lines]
>
> End Sub

Signature

Dave Peterson

Reply to this Message

CH - 20 Dec 2006 17:21 GMT

> This looks more natural as If/then/Else that select case (to me, anyway):
>
[quoted text clipped - 29 lines]
> >
> > End Sub

Thanks Dave. This seems to work.

ch

Reply to this Message

Don Guillett - 20 Dec 2006 18:44 GMT

Unless you have a lot of these, in this limited case (pun intended) I would
use a simple if instead

with sheets("car")
.rows.hidden=false
if ucase(range("a12")="DOG" and ucase(range("b16")="CAR" then _
.rows("1:20").hidden=true
end with

Signature

Don Guillett
SalesAid Software
dguillett1@austin.rr.com

>I have the following code below in which I want it to check the value
> of two cells and if the two cells = certain values then hide rows on a
[quoted text clipped - 16 lines]
>
> End Sub

Reply to this Message

Rate this thread: