Hi, an follow-up addy...:)
Is it possible to fill in the remaining cells as we move along clearing the
drawn list numbers on column C1:C35 ?
Martyn
"Bob Phillips" wrote in message
...
Sub Rast()
Dim say As Integer
Dim ara As Range
Dim RS As Integer
Dim oCell As Range
say = WorksheetFunction.CountA(Range("B1:B35")) + 1
If say = 36 Then Exit Sub
Randomize
again:
RS = Int((Rnd * 35) + 1)
For Each ara In Range("B1:B" & say)
If ara.Value = RS Then
GoTo again
End If
Next ara
If RS = 1 Then
Debug.Print RS
End If
Range("A1") = RS
Cells(say, 2) = RS
Set oCell = Columns(3).Find(RS, lookat:=xlWhole)
If Not oCell Is Nothing Then oCell.ClearContents
End Sub
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Martyn Wilson" wrote in message
...
Hi,
The below code is for drawing random numbers between 1-35 (not
repeating).
The drawn number is being displayed in A1 and at the same time beeing
added
on column B1:B35 as the macro is executed repetedly via a control
button.
So
far so good. But I need to add a new dimension to this code:
I am also displaying the numbers 1...35 on column C1:C35 and as I go
along
selecting random numbers via the macro, I want the drawn number on
column
C
to disappear one after the other as well...How is this achieved?
TIA
----------------------
Sub Rast()
Dim say As Integer
Dim ara As Range
Dim RS As Integer
say = WorksheetFunction.CountA(Range("B1:B35")) + 1
If say = 36 Then Exit Sub
Randomize
again:
RS = Int((Rnd * 35) + 1)
For Each ara In Range("B1:B" & say)
If ara.Value = RS Then
GoTo again
End If
Next ara
Range("A1") = RS
Cells(say, 2) = RS
End Sub
----------------------------------------
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.714 / Virus Database: 470 - Release Date: 02.07.2004
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (
http://www.grisoft.com).
Version: 6.0.715 / Virus Database: 471 - Release Date: 04.07.2004