randbetween excluding multiple values

[justinp2]

Hello.

I've found a VBA script that will randomly select a value from within a given range, and will exclude a range of values.

I am trying to modify it to exclude two ranges of values, but am pretty lost. Ideally what it will do is return an integer from the given range "excluding" the values given in Exclude1 and Exclude2.

Could someone help me tweak this?

Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude1 As Range, Exclude2 As Range) As Long
Dim R As Long
Dim C As Range

Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In Exclude1
If R = C Then Exit For
Next C
Loop Until C Is Nothing

RandBetweenInt = R
Application.Volatile
End Function

 

[CodeNinja]

Try something like:

Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude1 As Range, Exclude2 As Range) As Long
Dim R As Long
Dim C As Range
Dim JoinRange As Range

Set JoinRange = Union(Exclude1, Exclude2)


Do
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
For Each C In JoinRange
If R = C Then Exit For
Next C
Loop Until C Is Nothing

 

[JoeMo]

To avoid the need to loop through two exclusion ranges this might be faster if those ranges are extensive.

Function RandBetweenInt(Lowest As Long, Highest As Long, Exclude1 As Range, Exclude2 As Range) As Long
Dim R As Long, n As Long
Do
n = n + 1
R = Lowest + Int(Rnd() * (Highest + 1 - Lowest))
If Application.CountIf(Exclude1, R) = 0 And _
    Application.CountIf(Exclude2, R) = 0 Then Exit Do
Loop Until n = 1000 'can remove the n=1000 part, just here to avoid if everything between lo & hi is excluded
RandBetweenInt = R
Application.Volatile
End Function

 

[justinp2]

Thank you both of these worked well.

I have a tangentially related question but will start a new thread.
Justin