Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Select
Hi, Ive got 3 sheets of data, doesnt completly fill each sheet but
almost. Id like to randomly select a block of cells in a columm. eg a1:a100, a100:a200 etc. across the 3 sheets. Im guessing id randomly select the sheet then select the cells. Then Id like to loop through the selected cells. Any ideas please. Regards Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Select
Sub pick_a_bunch()
' 'pick a randon sheet ' n = Evaluate("Randbetween(1,3)") Sheets(n).Activate ' 'pick a random column ' cols = "randbetween(1," & Columns.Count & ")" col = Evaluate(s) ' 'pick random row range ' s = "randbetween(1," & Rows.Count & ")" rStart = Evaluate(s) s2 = "randbetween(" & rStart & "," & Rows.Count & ")" rEnd = Evaluate(s2) For i = rStart To rEnd MsgBox (Cells(i, cols).Address) Next End Sub -- Gary''s Student - gsnu2007 "RobcPettit" wrote: Hi, Ive got 3 sheets of data, doesnt completly fill each sheet but almost. Id like to randomly select a block of cells in a columm. eg a1:a100, a100:a200 etc. across the 3 sheets. Im guessing id randomly select the sheet then select the cells. Then Id like to loop through the selected cells. Any ideas please. Regards Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Select
Try this
Sub rancells() Const MaxRow = 1000 Dim RandRange(3) For shtcount = 1 To 3 Num1 = (MaxRow * Rnd()) + 1 Num2 = (MaxRow * Rnd()) + 1 If mum1 < Num2 Then FirstRow = Int(Num1) LastRow = Int(Num2) Else FirstRow = Int(Num2) LastRow = Int(Num1) End If With Sheets(shtcount) Set RandRange(shtcount - 1) = _ .Range("A" & FirstRow & ":A" & _ LastRow) End With Next shtcount For shtcount = 1 To 3 For Each cell In RandRange(shtcount - 1) 'enter your code here Next cell Next shtcount End Sub "RobcPettit" wrote: Hi, Ive got 3 sheets of data, doesnt completly fill each sheet but almost. Id like to randomly select a block of cells in a columm. eg a1:a100, a100:a200 etc. across the 3 sheets. Im guessing id randomly select the sheet then select the cells. Then Id like to loop through the selected cells. Any ideas please. Regards Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Random Select
Thankyou both for your help. Done the trick. Sorry for delay in reply
been away. Much appreciated. Regards Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a random sample | Excel Discussion (Misc queries) | |||
Random Select one of two or more numbers | Excel Programming | |||
How can I select random whole nos from 1-16 without repeating? | Excel Programming | |||
Select random cell | Excel Worksheet Functions | |||
Select a random cell < 1 | Excel Programming |