You can avoid using "on error.." by using the IsError
function:

validValues = Array(1, 3, 5, 7, 11)

if IsError(Application.Match(x, validValues, 0)) then
' x is not in list
Else
' x is in list
End If

I don't expect it makes much difference either way
in terms of efficiency, but it's more maintainable and
less error-prone.



On Sep 28, 12:04 pm, Joel wrote:
You can use the worksheet functtion MATCH(). the prblem witth using this
method if the item is not found an error will occur. So you must handle the
errror

Variable = 7
On Error Resume Next
Results = WorksheetFunction.Match(Variable, Array(1, 3, 7, 12), 0)
On Error GoTo 0
If IsEmpty(Results) Then
MsgBox ("Did not find match")
End If



"David" wrote:
Hello

I've got a convaluted IF statement:

--

If Variable = 1 or Variable = 3 or Variable = 7 or Variable = 12 Then

--

Using other languages you could for example do something like this:

--

If Variable MATCHES (1,3,7,12) Then

--

What would be the best way using VBA? Do you increase the efficiency
of the code by adding brackets etc, as in PICK Basic?

Any other general efficiency tips would be appreciated as I've been
asked to update some complex code that was written a long time ago by
one of my ex-colleagues and is deemed inefficient, but I'm not expert
when it comes to VBA, as you probably noticed!

Thanks!- Hide quoted text -

- Show quoted text -