Hello Excel community,
I've recently fell in love with Excel VBA about 4 months ago, and I have learned a bunch in this short period of time. Usually I find the answer to something I need relatively quickly, but I have stumped myself and hope you may get some enjoyment trying to help me figure this out.
Basically I would like to choose a random option based on a list that can grow to any size. The options to choose from will be in a particular range, but the range may be 2 words or 20 words. I have messed around with a few different ways of trying this and have gotten close, but no cigar. I'll paste where I left off with the code. Any help is appreciated for this nooby .
Thanks,
Phil
I've recently fell in love with Excel VBA about 4 months ago, and I have learned a bunch in this short period of time. Usually I find the answer to something I need relatively quickly, but I have stumped myself and hope you may get some enjoyment trying to help me figure this out.
Basically I would like to choose a random option based on a list that can grow to any size. The options to choose from will be in a particular range, but the range may be 2 words or 20 words. I have messed around with a few different ways of trying this and have gotten close, but no cigar. I'll paste where I left off with the code. Any help is appreciated for this nooby .
Thanks,
Phil
VBA Code:
Function RandBetween(Low As Integer, High As Integer) As Integer
RandBetween = WorksheetFunction.RandBetween(Low, High)
End Function
Function choose(CellRange As Range) As String
Dim Cell As Range
Dim x As Integer
x = CellRange.Cells.Count
choose = WorksheetFunction.choose(RandBetween(1, x), concat(CellRange))
End Function
Function concat(CellRange As Range) As Collection
Set concat = New Collection
Dim Cell As Range
Dim x As Integer
x = CellRange.Cells.Count
For Each Cell In CellRange.Cells
concat.Add Cell.Value
Next Cell
End Function
Last edited by a moderator: