How do I create a query field in Excel
I have a sheet with 9000 part numbers on it. Is there a way of say at the top
of the sheet having some sort of search box, so I can type in the part# and it goes straight to that part? Thanks |
Hi,
Method1: Using the Filter feature. -select a cell in the data -goto menu DataFilterAutoFilter - now your data header has dopdown boxes. From the part# header select the part#. This will make all show only rows with this part# Method2: -Select column Part# - menu Edit Find Method 3: with code - In a code module, paste the following sub FindAndGo Also change the 'CHANGE HERE' section to fit your particular case '------------------------------------------------------------------ Sub FindAndGo() Dim rgEntry, rgPart As Range, rg As Range '--- CHANGE HERE ----- Set rgEntry = Range("C1") 'value to search for Set rgPart = Range("A:A") 'range of Part# to search '------------------------ 'Find it and go there Set rg = Range("A:A").Find(What:=rgEntry.Value, LookIn:=xlValues, _ LookAt:=xlWhole) If Not rg Is Nothing Then Application.Goto Reference:=Application.ConvertFormula( _ rg.Address, xlA1, xlR1C1, True), scroll:=True End If End Sub '------------------------------------------------------------- - in the above code, the searched value is in C1 Next to this cell, add a button from the Forms toolbar Right Click the button and choose Assign Macro from the pop-up menu. Choose the FindAndGo macro from the list Now enter a value in C1 and click the button. If the value exists, you'll be sent to the corresponding cell. -- Regards, Sébastien "Sean" wrote: I have a sheet with 9000 part numbers on it. Is there a way of say at the top of the sheet having some sort of search box, so I can type in the part# and it goes straight to that part? Thanks |
All times are GMT +1. The time now is 05:09 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com