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