Function to jump to row or something similar

[cryss1988]

Hi all
Thanks in advance for your help.
I have been trying to find something to help me make my excel spreadsheet work, nut no luck.
So here it is.
I have a list on hundreds of parts, a few columns, like part number, description, infor, and locations.

I'm interested in:
A way of quickly find in that spreadsheet, searching by (partial) description only
get the results and either jump to that row
Or
Load that row completely at the top of the spreadsheet.

So far I managed to use VLookup and Match functions to get it work, but I'm only getting the row number where my part is,
I would like it to jumpnto that row or, like ai said, load the whole row completely

 

[RobP]

Hi welcome, if you have played with Macros, you could use something like this :

Sub findtest()

Dim ws As Worksheet, Found As Range
Set ws = ActiveWorkbook.ActiveSheet

'searchno = InputBox("Enter your Pat Number:", "Part No:")
'Set Found = ws.Columns(1).Find(what:=searchno, LookIn:=xlValues, lookat:=xlWhole) 'columns(1) = look at col 1 (A)

searchno = InputBox("Enter your Description:", "Description:")
Set Found = ws.Columns(2).Find(what:=searchno, LookIn:=xlValues, lookat:=xlPart)  'columns(2) = look at col. 2 or "B".

If Not Found Is Nothing Then
    Application.Goto Found
End If
End Sub

I included 2 options (one is Greened out by apostrophe at the start) - so you can use either one as you prefer.

One searches for a partial description in column B (assuming Col B is your description), and takes you to the nearest / first row.

The other (greened out) will search for an exact match of part number and take you to the row, assuming Column 1 or ("A") contains part numbers.

cheers
Rob

 

[cryss1988]

Thanks
But I'm not fully sure how to make that work.
Can you tell me how to

 

[cryss1988]

Sorry, I made it work, it's all fine wirh the pop up box for search...
but there is a small problem,
Can it be a excel field as the source for the search keyword

 

[RobP]

Hi,

in this case you wouldn't need the input box then I guess. You just point it to the cell ref.

searchno = InputBox("Enter your Description:", "Description:")

becomes

searchno = Activesheet.Range("H1")

if your cell was H1

Rgds
Rob

 

[cryss1988]

That's perfect thanks a lot!

Can we simplify this a bit, I'm thinking:
After inputing my search termn, is there a function to actually load the entire row with my search results?

I remember using something similar way way back.

 

[RobP]

not much of a limit with macros ... you just have to apply the logic ..

The danger is though when doing a partial search - that you update the wrong row .. so having eyes on is often a good thing as a sanity check. ?

what is it that gets updated (or why are you searching for the part ?)

cheers
Rob

 

[cryss1988]

not much of a limit with macros ... you just have to apply the logic ..

The danger is though when doing a partial search - that you update the wrong row .. so having eyes on is often a good thing as a sanity check. ?

what is it that gets updated (or why are you searching for the part ?)

cheers
Rob

So, the list won't get updated forba long time, and even if it does, will generate a new excel spreadsheet, copy the code or formula on it and it should work

So, is there a way of combining match or row functions? Or anything

 

[RobP]

to do what ?

 

[RobP]

or to do what you need in Formula (eg. paste row at top of page):

Book1
	A	B	C	D	E	F	G	H	I	J
1	12364	box of egg (12)	C4	0.87
2
3	12348	tin of beans	A1	0.2
4	12345	packet of spam	B2	0.25
5	12322	box of eggs (6)	L3	0.65				Search:	12364
6	12364	box of egg (12)	C4	0.87
7	12367	can of tuna	A5	1.65
8	12134	packet of corn flakes	B6	1.01
9	12254	box of pasta	D4	0.97
10	12222	sachet of sauce	F9	0.35
11	14321	bottle of milk	F8	1.56
12	54321	carton of cream	V4	1.24
13
Sheet1

Cell Formulas
Range	Formula
A1:D1	A1	=XLOOKUP(I5,A3:A12,A3:D12,"Not Found",1,1)
Dynamic array formulas.