Newbie...Select Case Statement
Newbie...Select Case Statement
(OP)
A couple of Questions
1) I'm wondering if a Select Case Statement can have an "And" in it?
2) If so, then...This code does seem to be working incorrectly, it puts the opposite in of what I have in the cells that it looks at. (see code below for details)
Example....If G36 = 4 and G37 = 1.5, It puts in 4x1.25. Why is that??
3) Also when I left last night it worked, (incorrectly but it worked) This morning it doesn't, What's up?
Here is my code:
Dim RiserQD3 As String
Select Case RiserQD3
Case Range("G36") = 4 And Range("G39") = 1.5
Range("G45").Select
ActiveCell = "4x1.5"
Case Range("G36") = 4 And Range("G39") = 1.25
Range("G45").Select
ActiveCell = "4x1.25"
Case Range("G36") = 3 And Range("G39") = 1.5
Range("G45").Select
ActiveCell = "3x1.5"
Case Range("G36") = 3 And Range("G39") = 1.25
Range("G45").Select
ActiveCell = "3x1.25"
Case Range("G36") = 2.5 And Range("G39") = 1.5
Range("G45").Select
ActiveCell = "2.5x1.5"
Case Range("G36") = 2.5 And Range("G39") = 1.25
Range("G45").Select
ActiveCell = "2.5x1.25"
Case Range("G36") = 2 And Range("G39") = 1.5
Range("G45").Select
ActiveCell = "2x1.5"
Case Range("G36") = 2 And Range("G39") = 1.25
Range("G45").Select
ActiveCell = "2x1.25"
End Select
Thanks to anyone that can help me understand what's going on and where I made my mistake.
1) I'm wondering if a Select Case Statement can have an "And" in it?
2) If so, then...This code does seem to be working incorrectly, it puts the opposite in of what I have in the cells that it looks at. (see code below for details)
Example....If G36 = 4 and G37 = 1.5, It puts in 4x1.25. Why is that??
3) Also when I left last night it worked, (incorrectly but it worked) This morning it doesn't, What's up?
Here is my code:
Dim RiserQD3 As String
Select Case RiserQD3
Case Range("G36") = 4 And Range("G39") = 1.5
Range("G45").Select
ActiveCell = "4x1.5"
Case Range("G36") = 4 And Range("G39") = 1.25
Range("G45").Select
ActiveCell = "4x1.25"
Case Range("G36") = 3 And Range("G39") = 1.5
Range("G45").Select
ActiveCell = "3x1.5"
Case Range("G36") = 3 And Range("G39") = 1.25
Range("G45").Select
ActiveCell = "3x1.25"
Case Range("G36") = 2.5 And Range("G39") = 1.5
Range("G45").Select
ActiveCell = "2.5x1.5"
Case Range("G36") = 2.5 And Range("G39") = 1.25
Range("G45").Select
ActiveCell = "2.5x1.25"
Case Range("G36") = 2 And Range("G39") = 1.5
Range("G45").Select
ActiveCell = "2x1.5"
Case Range("G36") = 2 And Range("G39") = 1.25
Range("G45").Select
ActiveCell = "2x1.25"
End Select
Thanks to anyone that can help me understand what's going on and where I made my mistake.
Scott Baugh, CSWP ![]()
George Koch Sons,LLC
Evansville, IN 47714
sjb@kochllc.com





RE: Newbie...Select Case Statement
1) I'm wondering if a Select Case Statement can have an "And" in it?
Ans: A Select Case Statement can have an "And" in it provided the reference is made to the expression/variable of the 'Select Case' statement (read on..)
2) Select Case works like this: If a Case condition is satisfied, it executes the code and then skips all subsequent Case(s) even though conditions in one or more of them may evaluate to TRUE
I think the code u present here is different from that u tested - you may have made some modifications hwich u're not able to recall
3) Also when I left last night it worked, (incorrectly but it worked) This morning it doesn't, What's up?
Ans: You might not remember but u may have inserted the Dim AFTER it worked last night.
(see discussion below)
Comments:
Dim RiserQD3 As String
Select Case RiserQD3
Once u use the Dim statement RiserQD3 is set to a null string("")
The statement immediately after this is Select Case - Now, RiserQD3 has not been set to any meaningful string after Dim - so there will be no cases for RiserQD3 other than Case ""
When u use a 'Select Case <expression>...End Select' construct, each Case Statement contained within it should be a reference to <expression>, not something else like a range. As u see above the only case that existe for RiserQD3 is "" as u haven't set it to any other value - and what u're checking in the 'Case' statements is the contents of 'G36' and 'G39'.
What u are trying to achieve in the code can be done very easily with the following single statement without any elaborate VBA construct, for all the cases appearing in your code:
Range("G45") = ltrim(str(Range("G36")) + "x" + ltrim(str(Range("G39"))
- provided, of course, both cells contain numbers
RE: Newbie...Select Case Statement
Range("G45") = ltrim(str(Range("G36")) + "x" + ltrim(str(Range("G39"))
As:
Range("G45") = ltrim(str(Range("G36"))) + "x" + ltrim(str(Range("G39")))
The bracket balance was incorrect, sorry.
RE: Newbie...Select Case Statement
Thanks again,
Scott Baugh, CSWP
George Koch Sons,LLC
Evansville, IN 47714
sjb@kochllc.com
RE: Newbie...Select Case Statement
I must say I'm surprised at how, most of the time, I get an acknowledgement within minutes of posting a response. It is very encouraging - and goes to show all of u REALLY mean business.
Good luck, everyone!!