asked on

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.

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David

ASKER

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?

ASKER CERTIFIED SOLUTION

Jim Horn

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David

ASKER

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

SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David

ASKER

Thank you.

mbizup

A Select-Case block is like a very readable mega if-then-else block...

Jim Horn

Thanks for the split. Good luck with your project. -Jim