Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 22

Thread: there is a problem

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try testing the code at the top i think it is wrong infomation that someone gave you before.

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    which part do u think is wrong, the only problem i am having is what i have written wrote above.

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    using your suggested formula i am still getting the same problem.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Christchurch New Zealand
    Posts
    1,030
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #10
    New Member
    Join Date
    Apr 2002
    Posts
    38
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    by adding that it doesnt help

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •