this is my formula so far(below). but the problem is that if the user presses cancle at the input box then the message "Please choose correct ...." is shown. This shouldn't be shown: instead the formula the macro should stop. How can i alter the formula to ahieve this?
Sub RANDOM()
Dim x
x = Application.InputBox("Would you like to pair up 4, 8, 16, 32, 64 or 128 players" _
, 4, , , , 1)
If x<> 4 And x<> 8 And x<> 16 And x<> 32 And x<> 64 And x<> 128 Then MsgBox "Please choose correct number of players to pair up"
If x = 4 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A4"), Type:=xlFillDefault
Range("A1:A4").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B4"), Type:=xlFillDefault
Range("B1:B4").Select
Range("C1").Select
End If
If x = 8 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A8"), Type:=xlFillDefault
Range("A1:A8").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B8"), Type:=xlFillDefault
Range("B1:B8").Select
Range("C1").Select
End If
If x = 16 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A16"), Type:=xlFillDefault
Range("A1:A16").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B16"), Type:=xlFillDefault
Range("B1:B16").Select
Range("C1").Select
End If
If x = 32 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A32"), Type:=xlFillDefault
Range("A1:A32").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B32"), Type:=xlFillDefault
Range("B1:B32").Select
Range("C1").Select
End If
If x = 64 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A64"), Type:=xlFillDefault
Range("A1:A64").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B64"), Type:=xlFillDefault
Range("B1:B64").Select
Range("C1").Select
End If
If x = 128 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A128"), Type:=xlFillDefault
Range("A1:A128").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B128"), Type:=xlFillDefault
Range("B1:B128").Select
Range("C1").Select
End If
End Sub
This message was edited by MERKY on 2002-05-01 15:09
Sub RANDOM()
Dim x
x = Application.InputBox("Would you like to pair up 4, 8, 16, 32, 64 or 128 players" _
, 4, , , , 1)
If x<> 4 And x<> 8 And x<> 16 And x<> 32 And x<> 64 And x<> 128 Then MsgBox "Please choose correct number of players to pair up"
If x = 4 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A4"), Type:=xlFillDefault
Range("A1:A4").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B4"), Type:=xlFillDefault
Range("B1:B4").Select
Range("C1").Select
End If
If x = 8 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A8"), Type:=xlFillDefault
Range("A1:A8").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B8"), Type:=xlFillDefault
Range("B1:B8").Select
Range("C1").Select
End If
If x = 16 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A16"), Type:=xlFillDefault
Range("A1:A16").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B16"), Type:=xlFillDefault
Range("B1:B16").Select
Range("C1").Select
End If
If x = 32 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A32"), Type:=xlFillDefault
Range("A1:A32").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B32"), Type:=xlFillDefault
Range("B1:B32").Select
Range("C1").Select
End If
If x = 64 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A64"), Type:=xlFillDefault
Range("A1:A64").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B64"), Type:=xlFillDefault
Range("B1:B64").Select
Range("C1").Select
End If
If x = 128 Then
Range("A1").Select
ActiveCell.FormulaR1C1 = "=RAND()"
Selection.AutoFill Destination:=Range("A1:A128"), Type:=xlFillDefault
Range("A1:A128").Select
Range("B1").Select
ActiveCell.FormulaR1C1 = "=RANK(RC[-1],C[-1])"
Selection.AutoFill Destination:=Range("B1:B128"), Type:=xlFillDefault
Range("B1:B128").Select
Range("C1").Select
End If
End Sub
This message was edited by MERKY on 2002-05-01 15:09