CODEWITHSUNDEEP
vbaexcel
RiverBanana
RiverBanana

Reputation: 120

Finding at least one item in a VBA array in a range

I'm currently trying to find at least one item from my array in a range before proceeding, but the way I'm currently doing it checks that all values are in the range.

If Not accountRange.find(Array(11571, 11572, 11573, 11574, 11575)) Is Nothing Then

Ideally I'd have something like this:

If Not accountRange.find(Array(11571, xlOr, 11572, xlOr, 11573, xlOr, 11574, xlOr, 11575))

The only workaround I can see is checking each condition individually, like so:

If Not accountRange.find(11571) Is Nothing Or _ 
    Not accountRange.Find(11572) Is Nothing Or _
    Not accountRange.Find(11573) Is Nothing Or _
    Not accountRange.Find(11574) Is Nothing Or _
    Not accountRange.Find(11575) Is Nothing Then

but obviously this will get tedious when I have more things to check in the range.

Any suggestions?

Upvotes: 2

Views: 427

Answers (1)

John Coleman
John Coleman

Reputation: 51998

You could write a function like this:

Function ValInRange(vals As Variant, R As Range) As Boolean
    Dim item As Variant
    For Each item In vals
        If Not R.Find(item) Is Nothing Then
            ValInRange = True
            Exit Function
        End If
    Next item
End Function

Then ValInRange(Array(11571, 11572, 11573, 11574, 11575), accountRange) will return True if one of the passed numbers is in the range. Otherwise, it returns the default Boolean value of False.

Upvotes: 3

Related Questions