Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 8 of 8

Thread: I need helpfast

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

    Default

    how can i alter this formula so that it shows the Top number of players that the user types into the input box
    Thankz in advance

    Sub Filter_Ltable()
    '
    ' Filter_Ltable Macro
    ' Macro recorded 10/05/2002 by
    '

    '

    Dim x As Integer
    x = Application.InputBox("How many of the top players do you want to show" _
    , "Enter number of players", 10, , , 1)

    Range("B4:H4").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=7, Criteria1:="10", Operator:=xlTop10Items
    Range("B5").Select
    End Sub

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Change:

    Selection.AutoFilter Field:=7, Criteria1:="10", Operator:=xlTop10Items

    To:

    Selection.AutoFilter Field:=7, Criteria1:=x, Operator:=xlTop10Items

    Tom

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

    Default

    Tom, your formula only works if the user enters 10 into input box. I need it to work for any number

    [ This Message was edited by: MERKY on 2002-05-09 17:53 ]

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    I don't know what your list is.
    I don't know why it is not working???
    Did you change the criteria to x?

    Criteria1:=x

    Tom


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

    it works alright on mine too

  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

    try it like this

    Sub Filter_Ltable()


    Dim x
    x = (CLng(Application.InputBox("How many of the top players do you want to show" _
    , "Enter number of players")))

    range("b4":"h4").select
    If Selection.AutoFilterMode = False Then Selection.AutoFilterMode
    Selection.AutoFilter Field:=7, Criteria1:=(x), Operator:=xlTop10Items
    Range("B5").Select
    End Sub


  7. #7
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Selection.AutoFilter Field:=7, Criteria1:=(x), Operator:=xlTop10Items
    Hi brettvba,

    Please let me know the advantage to use ().
    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

  8. #8
    MrExcel MVP
    Colo's Avatar
    Join Date
    Mar 2002
    Location
    Kobe, Japan
    Posts
    1,456
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Both of TsTom's code and brettvba's code are works on mine.


    Microsoft Excel - Book2
    File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
    C1=
    ?BCDEFGH
    4Code NumSomethingSomethingSomethingSomethingSomethingScore
    5A000111111100
    6A00022221250
    7A000333333150
    8A000444444200
    9A00055555530
    10A000666666250
    11A000777777100
    12A000888888300
    13A000999999350
    14A00101010101010220
    15A00111111111111190
    16A00121212121212180
    17A0013131313131350
    18A0014141414141450
    19A0015151515151530
    20A0016161616161610
    Sheet1

    Assuming your sheet is like above image.




    brettvba, I always do not use () but my code is works fine too.


    Sub Filter_Ltable()
    Const m1 As String = "How many of the top players do you want to show"
    Const m2 As String = "Enter number of players"
    Dim x As Integer

    x = CInt(Application.InputBox(m1, m2, Type:=1))
    With Range("B4:H4")
    If Not .Parent.AutoFilterMode Then .AutoFilter
    .AutoFilter 7, x, 3
    End With
    End Sub

    Hope this helps + pen pineapple apple pen!

    Masaru Kaji aka Colo - cellmasters.net

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
  •