×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Newbie...Select Case Statement

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.

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

RE: Newbie...Select Case Statement

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("")

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

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

(OP)
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

RE: Newbie...Select Case Statement

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources