Select Case Not Returning Correct String

[RyanH]

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

 

[Jon Peltier]

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 restructure:

Select Case True
Case L <= 18

Case L <= 24

Case L <= 30

'etc.

End Select

- Jon

 

[Don Guillett]

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

 

[Ron Rosenfeld]

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