Thread: about random no

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.

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" <jinvictor.2a03bz_1151310602.6774@excelforum-nospam.com> wrote
in message news:jinvictor.2a03bz_1151310602.6774@excelforum-nospam.com...
>
> 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
>