My Select Case is not returning the correct string. For example, when L =
39, my code always returns "18'' T12 (F18T12HO)" and not "36'' T12
(F36T12HO)", why?

I think VBA is just looking at the first part of Case and not the part after
the AND. I'm not sure how to force it to look at the part after the AND.

Sub cmbEstimate_Click()

Select Case L = Lwidth - Sockets

Case L < 18
MsgBox "This cabinet is too small to use Fluorescents.", vbCritical
Exit Sub

Case 18 < L And L <= 24
cboLamps1 = "18'' T12 (F18T12HO)"

Case 24 < L And L <= 30
cboLamps1 = "24'' T12 (F24T12HO)"

Case 30 < L And L <= 36
cboLamps1 = "30'' T12 (F30T12HO)"

Case 36 < L And L <= 42
cboLamps1 = "36'' T12 (F36T12HO)"

End select

End sub

Thanks in Advance,
Ryan

Select Case L = Lwidth - Sockets

is evaluating

L = Lwidth - Sockets

which is either true or false. Use this:

L = Lwidth - Sockets
Select Case L

Then your cases need to deal with the value of L but not include L.

You also don't need two parts, just put them in order. Let's restructu

Select Case True
Case L <= 18

Case L <= 24

Case L <= 30

'etc.

End Select

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"RyanH" wrote in message
...
My Select Case is not returning the correct string. For example, when L =
39, my code always returns "18'' T12 (F18T12HO)" and not "36'' T12
(F36T12HO)", why?

I think VBA is just looking at the first part of Case and not the part
after
the AND. I'm not sure how to force it to look at the part after the AND.

Sub cmbEstimate_Click()

Select Case L = Lwidth - Sockets

Case L < 18
MsgBox "This cabinet is too small to use Fluorescents.", vbCritical
Exit Sub

Case 18 < L And L <= 24
cboLamps1 = "18'' T12 (F18T12HO)"

Case 24 < L And L <= 30
cboLamps1 = "24'' T12 (F24T12HO)"

Case 30 < L And L <= 36
cboLamps1 = "30'' T12 (F30T12HO)"

Case 36 < L And L <= 42
cboLamps1 = "36'' T12 (F36T12HO)"

End select

End sub

Thanks in Advance,
Ryan

Try this idea instead

Sub categorize()
'L = 17
'MsgBox Int(L / 6) * 6

L=Lwidth - Sockets
If L < 18 Then
MsgBox "Too small"
Else
'MsgBox "T12 (F" & Int(L / 6) * 6 & "T12HO)"
cbolamps1 = "T12 (F" & Int(L / 6) * 6 & "T12HO)"
End If
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"RyanH" wrote in message
...
My Select Case is not returning the correct string. For example, when L =
39, my code always returns "18'' T12 (F18T12HO)" and not "36'' T12
(F36T12HO)", why?

I think VBA is just looking at the first part of Case and not the part
after
the AND. I'm not sure how to force it to look at the part after the AND.

Sub cmbEstimate_Click()

Select Case L = Lwidth - Sockets

Case L < 18
MsgBox "This cabinet is too small to use Fluorescents.", vbCritical
Exit Sub

Case 18 < L And L <= 24
cboLamps1 = "18'' T12 (F18T12HO)"

Case 24 < L And L <= 30
cboLamps1 = "24'' T12 (F24T12HO)"

Case 30 < L And L <= 36
cboLamps1 = "30'' T12 (F30T12HO)"

Case 36 < L And L <= 42
cboLamps1 = "36'' T12 (F36T12HO)"

End select

End sub

Thanks in Advance,
Ryan

On Fri, 15 Feb 2008 06:27:02 -0800, RyanH
wrote:

My Select Case is not returning the correct string. For example, when L =
39, my code always returns "18'' T12 (F18T12HO)" and not "36'' T12
(F36T12HO)", why?

I think VBA is just looking at the first part of Case and not the part after
the AND. I'm not sure how to force it to look at the part after the AND.

Sub cmbEstimate_Click()

Select Case L = Lwidth - Sockets

Case L < 18
MsgBox "This cabinet is too small to use Fluorescents.", vbCritical
Exit Sub

Case 18 < L And L <= 24
cboLamps1 = "18'' T12 (F18T12HO)"

Case 24 < L And L <= 30
cboLamps1 = "24'' T12 (F24T12HO)"

Case 30 < L And L <= 36
cboLamps1 = "30'' T12 (F30T12HO)"

Case 36 < L And L <= 42
cboLamps1 = "36'' T12 (F36T12HO)"

End select

End sub

Thanks in Advance,
Ryan

How are you setting the value of L? (Your code does not do that).

There is nothing in the code you posted indicating that you have ever set L
equal to something.

Your Select Case statement will return TRUE or FALSE. True will always
evaluate to less than 18, so your first Case statement will always be the one
that is executed.

You need to read HELP for Operator Precedence and also for the Select Case
function.

Perhaps something like (not-tested):
l = lwidth - sockets
Select Case L
Case < 18
...
case 18 to 24
...
case 25 to 30
...

--ron