I don't want to repeat the same number
 

how can I change a repeating number?
this is what I have so far.
Thank you!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
A = Cells(1, 1) + 2
Dim Rw, Col
Rw = Target.Row: Col = Target.Column
If Col < 2 Then Exit Sub
If Rw < 3 Or Rw A Then Exit Sub
If Cells(Rw, Col) = Cells(Rw, Col) Then
Cells(Rw, Col) = Int((A * Rnd) + 1)
Else: Exit Sub
End If
End Sub

I don't want to repeat the same number
 

Try these modifications

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rw, Col

A = Cells(1, 1) + 2


Rw = Target.Row: Col = Target.Column


If Col < 2 Then Exit Sub
If Rw < 3 Or Rw A Then Exit Sub

For i = 0 To (A - Rw - 1)

For J = i + 1 To (A - Rw)

Do While Cells(Rw, Col).Offset(Rowoffset:=i, columnoffset:=0) = _
Cells(Rw, Col).Offset(Rowoffset:=J, columnoffset:=0)

Cells(Rw, Col).Offset(Rowoffset:=J, columnoffset:=0) = _
Int((A * Rnd) + 1)
Loop
Next J
Next i

End Sub


"Sean" wrote:

how can I change a repeating number?
this is what I have so far.
Thank you!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
A = Cells(1, 1) + 2
Dim Rw, Col
Rw = Target.Row: Col = Target.Column
If Col < 2 Then Exit Sub
If Rw < 3 Or Rw A Then Exit Sub
If Cells(Rw, Col) = Cells(Rw, Col) Then
Cells(Rw, Col) = Int((A * Rnd) + 1)
Else: Exit Sub
End If
End Sub

I don't want to repeat the same number
 

Joel,
Thank you for you reply. Let me explain what I am trying to do. I run a
Luck of the Draw Dart Tournament. Normally I use playing cards to pair up
the partners I would like to use excel. I have a masters draw (best players
draw cards first), then ladies, then men. if you have any suggestions I would
greatly appreciate it.
Thank you,

Sean
"Joel" wrote:

Try these modifications

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Rw, Col

A = Cells(1, 1) + 2


Rw = Target.Row: Col = Target.Column


If Col < 2 Then Exit Sub
If Rw < 3 Or Rw A Then Exit Sub

For i = 0 To (A - Rw - 1)

For J = i + 1 To (A - Rw)

Do While Cells(Rw, Col).Offset(Rowoffset:=i, columnoffset:=0) = _
Cells(Rw, Col).Offset(Rowoffset:=J, columnoffset:=0)

Cells(Rw, Col).Offset(Rowoffset:=J, columnoffset:=0) = _
Int((A * Rnd) + 1)
Loop
Next J
Next i

End Sub


"Sean" wrote:

how can I change a repeating number?
this is what I have so far.
Thank you!

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
A = Cells(1, 1) + 2
Dim Rw, Col
Rw = Target.Row: Col = Target.Column
If Col < 2 Then Exit Sub
If Rw < 3 Or Rw A Then Exit Sub
If Cells(Rw, Col) = Cells(Rw, Col) Then
Cells(Rw, Col) = Int((A * Rnd) + 1)
Else: Exit Sub
End If
End Sub