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
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 Data>Filter>AutoFilter
- 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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks