• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2090
  • Last Modified:

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.
 
0
dddw
Asked:
dddw
  • 4
  • 3
  • 2
4 Solutions
 
mbizupCommented:
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
 
Jim HornMicrosoft 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now