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

--
jinvicto
-----------------------------------------------------------------------
jinvictor's Profile: http://www.excelforum.com/member.php...fo&userid=3409
View this thread: http://www.excelforum.com/showthread.php?threadid=55548

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

"jinvictor" wrote:


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 fill
with random numbers between 1-11, but the number in any of this column
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.


--
jinvictor
------------------------------------------------------------------------
jinvictor's Profile: http://www.excelforum.com/member.php...o&userid=34099
View this thread: http://www.excelforum.com/showthread...hreadid=555482

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)

"jinvictor" wrote
in message ...

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 fill
with random numbers between 1-11, but the number in any of this column
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.


--
jinvictor
------------------------------------------------------------------------
jinvictor's Profile:
http://www.excelforum.com/member.php...o&userid=34099
View this thread: http://www.excelforum.com/showthread...hreadid=555482