|
|
|
|||||||
| Excel Questions All Excel/VBA questions - formulas, macros, pivot tables, general help, etc. Please post to this forum in English only. |
 |
|
|
Thread Tools | Display Modes |
May 1st, 2002, 04:08 PM
|
#1 |
|
New Member
Join Date: Apr 2002
Posts: 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 ] |
|
|
|
May 1st, 2002, 04:27 PM
|
#2 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Christchurch New Zealand
Posts: 1,030
|
try testing the code at the top i think it is wrong infomation that someone gave you before.
|
|
|
|
|
May 1st, 2002, 04:32 PM
|
#3 | |
|
MrExcel MVP
Join Date: Feb 2002
Location: Christchurch New Zealand
Posts: 1,030
|
Quote:
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 |
|
|
|
|
|
May 1st, 2002, 04:33 PM
|
#4 |
|
New Member
Join Date: Apr 2002
Posts: 38
|
which part do u think is wrong, the only problem i am having is what i have written wrote above.
|
|
|
|
|
May 1st, 2002, 04:37 PM
|
#5 |
|
New Member
Join Date: Apr 2002
Posts: 38
|
using your suggested formula i am still getting the same problem.
|
|
|
|
|
May 1st, 2002, 04:42 PM
|
#6 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Christchurch New Zealand
Posts: 1,030
|
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. |
|
|
|
|
May 1st, 2002, 04:47 PM
|
#7 |
|
New Member
Join Date: Apr 2002
Posts: 38
|
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. |
|
|
|
|
May 1st, 2002, 04:50 PM
|
#8 |
|
MrExcel MVP
Join Date: Feb 2002
Location: Christchurch New Zealand
Posts: 1,030
|
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" |
|
|
|
|
May 1st, 2002, 04:53 PM
|
#9 |
|
MrExcel MVP
Join Date: Mar 2002
Location: Chicago, IL USA
Posts: 2,042
|
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
|
|
|
|
|
May 1st, 2002, 04:56 PM
|
#10 |
|
New Member
Join Date: Apr 2002
Posts: 38
|
by adding that it doesnt help
|
|
|
|
|
| Bookmarks |
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
