VBA pick random cell - print

[flashing frank]

hi,
i want to pick a random cell (containing text) in a given range.

i used the "randbetween" function in excel, but i am now needing thi
in visual basic.

i found the following vba snipplet, but i don´t understand it, an
can´t implement it in my code:


PHP code
-------------------
With Range("B1")
.FormulaR1C1 = _
"=OFFSET(Sheet1!RC[-1],RANDBETWEEN(0,9),0)"

-------------------
it would be great if someone could give me an example of picking
random cell in the range lets say ("A1:B10")

i would also like to know how to combine two random picks with th
print function.
something like:

Print #1, (function for random cell1 ("a1:B10")) & (function for rand
cell2 ("C3:C10"))

i hope i described my problem good enough...

thank

 

[Jim Rech]

it would be great if someone could give me an example of picking a
random cell in the range lets say ("A1:B10")

Okay, here such a function:

Function RandCell(Rg As Range) As Range
Set RandCell = Rg.Cells(Int(Rnd * Rg.Cells.Count) + 1)
End Function

and here's a test to satisfy yourself it's working. I'm sure you can hook
RandCell into your code.

Sub RandCellTest()
Dim Counter As Long
Dim TargetRg As Range
Application.ScreenUpdating = False
Set TargetRg = Range("A1:A10")
TargetRg.ClearContents
Dim Cell As Range
For Counter = 1 To 10000
Set Cell = RandCell(TargetRg)
Cell.Value = Cell.Value + 1
Next
End Sub

 

[flashing frank]

thanks works fine :