One method...........

Sub AskAndFilter_Select()
Dim c As String
Dim mycol As Range
Dim col As String
Dim d As Integer
Dim r As Range
Set r = Range("A1:G1000")
Set mycol = Application.InputBox(prompt:="Select a Column", Type:=8)
col = Left(mycol.Address, 2)
d = Columns(col).Column
c = InputBox("Enter Criteria")
r.AutoFilter Field:=d, Criteria1:=c
End Sub

Another..........

Sub AskAndFilter_Letter()
Dim c As String
Dim mycol As String
Dim r As Range
Set r = Range("A1:G1000")
mycol = InputBox("Type a column letter")
d = Columns(mycol).Column
c = InputBox("Enter Criteria")
r.AutoFilter Field:=d, Criteria1:=c
End Sub


Gord Dibben MS Excel MVP




On Thu, 3 Dec 2009 18:32:01 -0800, Durai
wrote:

Works very well. i clicked "yes'.
if I like to use either " item number' or "region" or "sold', then how can I
change the input box to ask accordingly. Please help.
Thanks

"FSt1" wrote:

hi
i think a small macro with a inputbox and filter might be what you want...
Sub AskAndFilter()
Dim c As String
Dim r As Range
Set r = Range("A1:G1000")
c = InputBox("Enter Item Number")
r.AutoFilter Field:=1, Criteria1:=c
End Sub

adjust range to fist your data.
you can run it from a command button on the sheet.

new to macro?? see this site....
http://www.mvps.org/dmcritchie/excel/getstarted.htm

regards
FSt1

"Durai" wrote:

i have details for parts in excel. Like to have a macro that will ask the
user for a item number and shaw all details for that item
example
Item# purchase sold date region
a0012 1200 400 12/12/09 Ont
b1115 500 200 12/01/09 Ont
a0012 300 50 12/05/09 Aberta
a0012 400 25 12/08/09 Ont

Thanks for any help