Selecting the value from a randomly selected cell out of a range

[Steve W.]

I am trying to select a value from a randomly selected cell out of a range of
values in a column to use in a formula in a second column. I can't seem to
find a way to do this?

 

[Tim879]

Try this... It will randomly pick a cell from the selected cells. The
first is a macro, the second is a worksheet function.

Option Explicit

Sub choose_random_cell_from_selection()
Dim MyValue
Randomize ' Initialize random-number generator.

MyValue = Int((Selection.Count * Rnd) + 1) ' Generate random value
between 1 and 6.

MsgBox Selection(MyValue)

End Sub


Function choose_random_cell(selected_range As Range)
Dim MyValue
Randomize ' Initialize random-number generator.

MyValue = Int((selected_range.Count * Rnd) + 1) ' Generate random
value between 1 and 6.

choose_random_cell = selected_range(MyValue)

End Function