(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)
            Set yRg = Union(yRg, xRg.Offset(0, 0   Col))
        End If
    End If
Next xRg

    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
End Function

Leave a Reply

Your email address will not be published. Required fields are marked *