Random number from Selection

[Forestq]

Hi,

I want to select random value from selection. I have just:

Dim my_range()
Range("A2").CurrentRegion.Select
my_range = Selection

How to use Random function to select random value from my selection?

 

[mikerickson]

Something like

Randomize
With Selection
    MsgBox .Item(Int(Rnd() * .Cells.Count)) + 1).Value
End With

 

[Forestq]

something is wrong with the dot

.Item, .Cells.Count, .Value

 

[Forestq]

below code works:

Range("A2").CurrentRegion.Select
my_range = Selection
RandomDay = Int((UBound(my_range) * Rnd))
MsgBox Selection(RandomDay)

 

[L. Howard]

Curious with the results I got with the following.
Where A2 to A12 = 1 through 11.
In column G2 to G12 =COUNTIF(F:F,1) to =COUNTIF(F:F,11)
The list below is the results of each formula.
Total entries in column F is 9060.
10,000 - 9060 = 940, so number 11 was chosen 940 times, but not entered in F column.

Regards,
Howard

Option Explicit

Sub xx()

Dim myrg As Variant, i As Long
Dim RandomDay As Variant

Range("A2").CurrentRegion.Select
myrg = Selection

For i = 1 To 10000
RandomDay = Int((UBound(myrg) * Rnd))
'MsgBox Selection(RandomDay)
Range("F" & Rows.Count).End(xlUp).Offset(1, 0) = Selection(RandomDay)
Next

End Sub

[TABLE="width: 54"]
<COLGROUP><COL style="WIDTH: 54pt" width=72><TBODY>[TR]
[TD="width: 72, bgcolor: transparent, align: right"]900[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]938[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]939[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]893[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]914[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]915[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]880[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]894[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]908[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]878[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0[/TD]
[/TR]
</TBODY>[/TABLE]