Newbie...Select Case Statement 1

[SBaugh]

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&quot = 4 And Range("G39&quot = 1.5

Range("G45&quot.Select
ActiveCell = "4x1.5"

Case Range("G36&quot = 4 And Range("G39&quot = 1.25

Range("G45&quot.Select
ActiveCell = "4x1.25"

Case Range("G36&quot = 3 And Range("G39&quot = 1.5

Range("G45&quot.Select
ActiveCell = "3x1.5"

Case Range("G36&quot = 3 And Range("G39&quot = 1.25

Range("G45&quot.Select
ActiveCell = "3x1.25"

Case Range("G36&quot = 2.5 And Range("G39&quot = 1.5

Range("G45&quot.Select
ActiveCell = "2.5x1.5"

Case Range("G36&quot = 2.5 And Range("G39&quot = 1.25

Range("G45&quot.Select
ActiveCell = "2.5x1.25"

Case Range("G36&quot = 2 And Range("G39&quot = 1.5

Range("G45&quot.Select
ActiveCell = "2x1.5"

Case Range("G36&quot = 2 And Range("G39&quot = 1.25

Range("G45&quot.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

 

[Mala]

SBaugh:
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("&quot

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 &quot;&quot; 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(&quot;G45&quot = ltrim(str(Range(&quot;G36&quot) + &quot;x&quot; + ltrim(str(Range(&quot;G39&quot)
- provided, of course, both cells contain numbers

 

[Mala]

Read:
Range(&quot;G45&quot = ltrim(str(Range(&quot;G36&quot) + &quot;x&quot; + ltrim(str(Range(&quot;G39&quot)

As:
Range(&quot;G45&quot = ltrim(str(Range(&quot;G36&quot)) + &quot;x&quot; + ltrim(str(Range(&quot;G39&quot))

The bracket balance was incorrect, sorry.

 

[SBaugh]

Thanks Mala, I appreciate your fast response and I'll try your suggestion. That's why I'm a newbie and your not.

Thanks again, Scott Baugh, CSWP
George Koch Sons,LLC
Evansville, IN 47714
sjb@kochllc.com

 

[Mala]

Friends,
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!!