# (Solved) – Excel VBA: User Defined Function to calculate unique random number based on criteria in another column I’m trying to make a UDF that calculates a unique random number that has not been used for each criteria.

So, example: You have a column of 10 cells, 5 with “Apple” and 5 with “Pear”. I want the formula to generate a number that has not been used for “Apple” before. So you can have a 2 for Apple and 2 for Pear, but not 2 for Apple twice. Another parameter would be a maximum number, which would be a cell or value.

This is what I have so far, however it just results in a “Value” error when entered.

The Col variable is how many columns the Rand result is away from the Criteria column. I was thinking I could just instead have it be a defined range, but I didn’t know how to do that.

``````Function RandBetweenIntCrit(Lowest As Long, Highest As Long, Criteria As Range, Criteria_Range As Range, Col As Long) As Long
Dim R As Long
Dim C As Range
Dim xRg As Range
Dim yRg As Range

For Each xRg In Criteria_Range
If xRg = Criteria Then
If yRg Is Nothing Then
Set yRg = xRg.Offset(0, 0   Col)
Else
Set yRg = Union(yRg, xRg.Offset(0, 0   Col))
End If
End If
Next xRg

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

RandBetweenIntCrit = R
Application.Volatile
End Function``````