there is a problem

M

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
 
Sort by date Sort by votes
this works

Sub RANDOM()
Dim x
On Error GoTo msgerr
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
msgerr:
MsgBox "Please choose correct number of players to pair up"
End Sub

or try jays idea.
 
Upvote 0

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
Hi Merky,

Sorry, I didn't mean to offend by anything I have stated. I wanted you to recognize the pattern in your program and see if you could eliminate the extra code.

Bye,
Jay
 
Upvote 0
no problem Jay.
Your fromula works really well but the only problem is that if the user leaves the box blank and presses "OK" then the Error window pops up.
Is there any way to stop that hapenning?
This message was edited by MERKY on 2002-05-01 16:07
 
Upvote 0
On 2002-05-01 16:06, MERKY wrote:
no problem Jay.
Your fromula works really well but the only problem is that if the user leaves the box blank and presses "OK" then the Error window pops up.
Is there any way to stop that hapenning?
This message was edited by MERKY on 2002-05-01 16:07
Click to expand...

Hi Merky,

Bracket the x = Application.InputBox statement with On Error Resume Next and On Error Goto 0

If zero or blank results and the user clicks OK, x = 0 and the program exits.

Re: the routine
The advantage of this approach is you can add or subtract from the MyArr list (e.g. if you want to add 256 players) without having to adjust any code.

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

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

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
This message was edited by Jay Petrulis on 2002-05-01 16:15
 
Upvote 0
brettva,
have u tried the formula u gave me because it has all the problems i started off with.
 
Upvote 0
On 2002-05-01 16:20, MERKY wrote:
sorry jay, i dont understand what u exactly mean. can u show me that line.
Click to expand...

Hi,

I copied it into the code in my previous message.

change this:

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


to

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

and you should be OK.
This message was edited by Jay Petrulis on 2002-05-01 16:24
 
Upvote 0
by the way Jay,
how can i get rid of the Question Mark that is on the Input box next to X
 
Upvote 0
On 2002-05-01 16:31, MERKY wrote:
by the way Jay,
how can i get rid of the Question Mark that is on the Input box next to X
Click to expand...

I have no idea. If it can be done, Ivan Moala or Colo or others who know about that stuff will jump in with the response.
 
Upvote 0
You must log in or register to reply here.

Similar threads

VinceF
  • Solved
Runtime Error 1004 with Macro
Replies
3
Views
164
Joe4
Joe4
P
  • Question
Help with Vba Code
Replies
3
Views
150
Coyotex3
C
C
  • Question
Loop/Repeat Macro
2
Replies
11
Views
360
kvsrinivasamurthy
K
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
931
Joe4
Joe4
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
310
kevin9999
K

Forum statistics

Threads
1,214,653
Messages
6,120,752
Members
448,989
Latest member
mariah3

Share this page

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