Hope I can express myself:
Say we have numbers 1...35 on column C1:C35. When we select a random number
(say 15) via our macro, the cell containing that number (C15) is cleared.
Now I wonder if we can move the rest of the remaining number list on column
C1:C35 upwards so that the "cleared" cells are pushed towards the bottom of
column C.
TIA
"Bob Phillips" wrote in message
...
Pardon, I do not understand.
Fill what cells, with what?
--
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, 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
---
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