can u fix this formula?

[MERKY]

i have a formula that creates random numbers but the problem is that this message keeps coming on to the screen "Please choose correct.....". I only want it to come up when the user has entered a wrong number, otherwise it should continue with the formula. Have i done something wrong. can u try and correct it for me. Thankz.
The formula is:

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 Or x <> 8 Or x <> 16 Or x <> 32 Or x <> 64 Or 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

[Jay Petrulis]

Without looking at your code, don't you want to exclude those 4, 8, 16, ... numbers?

Change the OR to AND and see if that works for you.

[MERKY]

IT WORKS.
but
NEW PROBLEM: when the input box appears and the user clicks on "CANCEL" or clicks on the X or clicks on "OK" without typing a number then the message appears "Please choose the ....."

[ This Message was edited by: MERKY on 2002-05-01 14:50 ]

[Jay Petrulis]

Hi Merky,

Don't start a new thread, continue on this one until your follow-up questions are answered, unless it truly is a separate request.

For your answer, after the input box line, use something like:

If x = False then Exit Sub

You will have to play around with this to see what is returned from the input box when the cancel is clicked (it may be "False" with the quotes if x is a string, for instance).

HTH,
Jay

[zacemmel]

I would suggest learning VBA instead of using the macro recorder. You will find VBA is very easy to grasp.

[barryt]

if x is 4 in this statement it will check if x is not 4 then it will check if it is not 8 and since 4 isn't 8 then it will give you your msgbox
If x <> 4 Or x <> 8 Or x <> 16 Or x <> 32 Or x <> 64 Or x <> 128) Then MsgBox

Here is a way i think will work for you
If Not (x = 4 Or x = 8 Or x = 16 Or x = 32 Or x = 64 Or x = 128) Then MsgBox

you said you got it working with the using and's
does it give the msgbox when you type in the wrong number