I need helpfast

[MERKY]

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

[Tom Schreiner]

Change:

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

To:

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

Tom

[MERKY]

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 ]

[Tom Schreiner]

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

[brettvba]

it works alright on mine too

[brettvba]

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

[Colo]

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

Hi brettvba,

Please let me know the advantage to use ().

[Colo]

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		=
?	B	C	D	E	F	G	H
4	Code Num	Something	Something	Something	Something	Something	Score
5	A0001	1	1	1	1	1	100
6	A0002	2	2	2	1	2	50
7	A0003	3	3	3	3	3	150
8	A0004	4	4	4	4	4	200
9	A0005	5	5	5	5	5	30
10	A0006	6	6	6	6	6	250
11	A0007	7	7	7	7	7	100
12	A0008	8	8	8	8	8	300
13	A0009	9	9	9	9	9	350
14	A0010	10	10	10	10	10	220
15	A0011	11	11	11	11	11	190
16	A0012	12	12	12	12	12	180
17	A0013	13	13	13	13	13	50
18	A0014	14	14	14	14	14	50
19	A0015	15	15	15	15	15	30
20	A0016	16	16	16	16	16	10
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