Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: there is a problem

  1. #11
    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

    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.

  2. #12
    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 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

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

    Default

    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 ]

  4. #14
    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

    put that error code in when an error occurs it goes to the msgbox.

  5. #15
    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

    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 ]
    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 ]

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

    Default

    brettva,
    have u tried the formula u gave me because it has all the problems i started off with.

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

    Default

    sorry jay, i dont understand what u exactly mean. can u show me that line.

  8. #18
    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

    On 2002-05-01 16:20, MERKY wrote:
    sorry jay, i dont understand what u exactly mean. can u show me that line.
    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 ]

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

    Default

    by the way Jay,
    how can i get rid of the Question Mark that is on the Input box next to X

  10. #20
    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

    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
    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.

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
  •