there is a problem

MERKY

New Member
Joined
Apr 29, 2002
Messages
38
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
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
try testing the code at the top i think it is wrong infomation that someone gave you before.
 
Upvote 0
On 2002-05-01 15:08, MERKY wrote:
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

sorry it was just your input box that was wrong try this

Sub RANDOM()
Dim x

x = (CLng(InputBox("Would you like to pair up 4, 8, 16, 32, 64 or 128 players", "Hello")))
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
 
Upvote 0
which part do u think is wrong, the only problem i am having is what i have written wrote above.
 
Upvote 0
the problem you had before was th input box

the default for input box's is string
so it was picking up anything as not equal to your numbers because they were recognising the input (x) as a letter.

it should work now it works on mine.
 
Upvote 0
there must be something wrong with this line:

x = (CLng(InputBox("Would you like to pair up 4, 8, 16, 32, 64 or 128 players", "Hello")))

:because the debuging window comes up and says runtime error.

This only happens if i press OK without entering any number in the input box and also happens when i press cancel and when i press the 'X' button on the input screen.
 
Upvote 0
add this to the top
On Error GoTo msgerr

and this to the bottom
msgerr:
MsgBox "Please choose correct number of players to pair up"
 
Upvote 0
Hi,

You are needlessly duplicating your code. Find how many players are to be paired and load it in a straight shot.

If the user clicks cancel, the program exits. If the user enters the wrong number of players, the prompt is given again.

Try,

Code:
Sub RANDOM()
Dim x As Integer, counter As Integer, y As Integer, MyArr
Randomize
MyArr = Array(4, 8, 16, 32, 64, 128)

x = Application.InputBox("Would you like to pair up 4, 8, 16, 32, 64 or 128 players" _
, "Enter number of players", 4, , , 1)

If x = 0 Then Exit Sub

counter = 0
For y = 0 To UBound(MyArr)
If CInt(x) = MyArr(y) Then counter = counter + 1
Next y

If counter <> 0 Then
    Range("A1:A" & x) = "=RAND()"
    Range("B1:B" & x) = "=RANK(RC[-1],C[-1])"
Else: Application.Run "RANDOM"
End If

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,101
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top