Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to ask for a item# and then show all details for that item
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to ask for a item# and then show all details for that item
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. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to ask for a item# and then show all details for that item
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to ask for a item# and then show all details for that it
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to ask for a item# and then show all details for that it
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to ask for a item# and then show all details for that it
Thanks a lot . Works well
"Gord Dibben" wrote: 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To find rate of each item from item.xls and to copy price.xls | Excel Discussion (Misc queries) | |||
How to set up pivot to show name of series item in bar chart | Charts and Charting in Excel | |||
Item numbers result in item description in next field in Excel | Excel Worksheet Functions | |||
select from item and show data filtred... | Excel Discussion (Misc queries) | |||
Selecting an Item from a List and getting a different item to pop. | Excel Worksheet Functions |