Macro to ask for a item# and then show all details for that item

[Durai]

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

 

[Shane Devenshire]

Hi,

Might consider using an autofilter instead of a macro - Data, Filter, Auto
Filter. Then open the filter in the Item# column and pick any number.

 

[FSt1]

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]

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

 

[Gord Dibben]

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

 

[Durai]

Thanks a lot . Works well

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






.