about random no

[jinvictor]

anyone can do a micro for me please?

a1 has 1 number and it changes manully by me.

say a1 is 7,
what i want is when i click the button(with micro), b1-b30 will fil
with random numbers between 1-11, but the number in any of this colum
can't be same as a1.
so, b1-b30 will have number between 1-11, but no 7 inside.
when i change a1 to 9, no 9 shows in b1-b30

 

[Guest]

Try:

Sub a()
with worksheets("Sheet1")
For i = 1 To 30
Do
n = Int(Rnd() * 11 + 1)
Loop Until n <>. Range("A1")
..Cells(i, "B") = n
Next i
end with
End Sub

HTH

 

[Bob Phillips]

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A1"
Dim tmp
Dim i As Long

On Error GoTo ws_exit:
Application.EnableEvents = False
For i = 1 To 30
Do
tmp = Int(Rnd() * 11 + 1)
Loop Until tmp <> Target.Value
Me.Cells(i, "B").Value = tmp
Next i

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)