VBA between statement

In VBA, how do I write an expression that limits BETWEEN to values.  For example:

If [Lookup]= 1 to 10 Then msgbox "found it"

The above doesn't work in VBA but this is what I want to do.  I want it to look for the values 1 to 10.

How can I make this work?

Thanks.
 
dddwAsked:
Who is Participating?
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
Select Case [Lookup]
   Case 1 to 10
      'do 1-10 stuff
   Case 11 to 20
      'do 11-20 stuff
   Case Else
       'do anything else stuff
End Select

>I should have elaborated a little more.
One of the more annoying asker issues is the continuous stream of follow-on questions, so yes please try to state all your requirements up front in the original question.  Thanks in advance.
0
 
mbizupConnect With a Mentor Commented:
If [Lookup] >= 1  AND [Lookup] <= 10 Then msgbox "found it"
0
 
dddwAuthor Commented:
This helps.  I should have elaborated a little more.  The field [Lookup] has 1 to 30 options.  I want it to find only the option from 10 to 20.  Would code If [Lookup] >= 10  AND [Lookup] <= 20 Then msgbox "found it" still work?  
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
mbizupConnect With a Mentor Commented:
>>  Would code If [Lookup] >= 10  AND [Lookup] <= 20 Then msgbox "found it" still work?

For that specific case -- yes (no harm in trying it)

That said, Jim's approach is far more flexible if there are any other missing details :-)

0
 
dddwAuthor Commented:
So would the following work.  Thanks for your patience.

Select Case [Lookup]
   Case 1 to 10
      If [Lookup] >= 1 AND [Lookup] <= 10 Then msgbox "Found 1-10"
   Case 11 to 20
      If [Lookup] >= 11 AND [Lookup] <= 20 Then msgbox "Found 11-20"
   Case Else
       msgbox "not found"
End Select
0
 
mbizupConnect With a Mentor Commented:
You're combining independant solutions, and creating redundant code.

That will work, but is unnecessarily complicated.

You have two solutions here:

- An If-Then statement
- A Select Case block

They both work fine independantly of one another.

Try coding them up -- and testing them...
0
 
dddwAuthor Commented:
Thank you.
0
 
mbizupCommented:
A Select-Case block is like a very readable mega if-then-else block...
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the split.  Good luck with your project.  -Jim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.