there is a problem

[MERKY]

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

 

[MERKY]

you should be able to do it, because my original code doesn't produce the Question Mark nor does the formula Brettva gave me.
This message was edited by MERKY on 2002-05-01 16:47

 

[Jay Petrulis]

Aha! Brettvba used the VBA input box while I was using the Excel input box.

x = InputBox(stuff)
vs.
x = Application.InputBox(stuff)

Essentially the same thing. Good catch on noticing the difference. I never would've noticed it.