Find a number in a list.

[Matt5353]

I have a list of numbers 0-8200. I can use the find function on the home tab but I wish to have a search box that will highlight the number selected but you have to scroll through the list to find it. Is there a way I can have a search box on my spreadsheet so when I type in the required number it will go straight to the number without scrolling through the long list of numbers?
Andrew

 

[Matt5353]

Contact Tracing Passport Use. With Booster.xlsm
	I
22
24-Jan-22

 

[Joe4]

Hmmm, you last image does not show very much.
You may want to practice a little in the "Test Here" forum first, to be sure that you are using it correctly.

 

[Matt5353]

Contact Tracing Passport Use. With Booster.xlsm
	I
22
24-Jan-22

Posted my info did you get what you needed

 

[Joe4]

Posted my info did you get what you needed

No. All you posted was the blank cell I22 (you can see this, can't you?).
I don't see how is that helpul.

 

[Matt5353]

No. All you posted was the blank cell I22 (you can see this, can't you?).
I don't see how is that helpul.

Contact Tracing Passport Use. With Booster.xlsm
	A			D	E	F	G	H	I	J
1	Totals			Main Dining Room	DV	NPR	B	Club No	Guest Details DV Documented Use a 0 Under Club No	Code i/o
2	70			Kenneth Wood	*			1		In
3				Alan Liddell	*			2		In
4	27/01/2022			Barry Goodin	DV			3		In
5	Main In			Margaret Reid	*			4		In
6	70			Brian Goodin	DV			5		In
7				Rebecca Weller	*			6		In
8				Emily West	*			7		In
9	Main Out			Matthw Warner	DV			8		In
10				Bernice Holland	*			9		In
11				Gavin Corboy	*			10		In
12				John Ewen	DV			11		In
13	Tab In			Peter Frewin	*			12		In
14				Ian Gilmore	*			13		In
15				Shane Englebretsen	*			14		In
16				Harris Edlin	*			15		In
17	Tab Out			Ivan Trevathan	DV			16		In
18				Malcolm Young	DV			17		In
19				Glenn Hood	DV			18		In
20				Deane Campbell	DV			19		In
21	When you ask a member if they are DV. Input the Data Into the Data Base. Members who have not given details an * will appear. Members Show Once			Mary-Ann Helem	*			20		In
22				Eva Barlow	*			21		In
23				Diane Eagar	DV			22		In
24				Paul Belcaster	*			23		In
25				Aileen Spargo	DV			24		In
26				Jeffrey Rippey	*			25		In
27				Bev Jeffery	*			26		In
28				Ross Manson	*			27		In
29				Stuart Robb	*			28		In
30				Neville Rau	*			29		In
31				Luka O'Brien	*			30		In
32				Bevin Downie	DV			31		In
33				Alan Plunkett	DV			32		In
34				Christine Roberts	*			33		In
35				Te Poihipi Phillips	*			34		In
36				Helen Watson	*			35		In
37				Garry Pittams	*			36		In
38				Robyn Floyd	*			37		In
39				Deborah Hurren	DV			38		In
40				Graeme Bascand	*			39		In
41				Judith Knapman	*			40		In
42				Kath Simmonds	DV			41		In
43				Robin King	DV			42		In
44				Sheryl Coyne	DV			43		In
45				Gordon Kay	DV			44		In
46				Heather Clark	*			45		In
47				Wayne Gundersen	*			46		In
48				Kirileigh Bennett	*			47		In
49				Billy Waterston	*			48		In
50				Steve Herbison	DV			49		In
51				Molly Reid	DV			50		In
52				Barry Lethbridge	DV			51		In
53				Denise Moorhouse	*			52		In
54				Heni Zimmermann	*			53		In
55				Clive Woodward	*			54		In
56				Clive Jenkins	*			55		In
57				John Coyne	dv			56		In
58				Chris Rapson	*			57		In
59				Carol Hubert	*			58		In
60				Graeme Lovell	DV			59		In
61				Rosalie Pattillo	*			60		In
62				William Spencer	DV			61		In
63				Janette Comer	DV			62		In
64				Ross Anderson	*			63		In
65				Kevin Walters	DV			64		In
66				Brian Porter	DV			65		In
67				Hugh Marston	DV			66		In
68				Pat Monaghan	*			67		In
69				Mary Fletcher	DV			68		In
70				Graham O'donnell	*			69		In
71				Barry Bowden	*			70		In
24-Jan-22

Cell Formulas
Range	Formula
D2:D71	D2	=IF(H2<>"",VLOOKUP(H2,'Data Base'!$B$2:$J$8201,4,FALSE),"")
E2:E71	E2	=IF(H2<>"",VLOOKUP(H2,'Data Base'!$B$2:$J$8201,5,FALSE),"")
F2:F71	F2	=IF(H2<>"",VLOOKUP(H2,'Data Base'!$B$2:$J$8201,9,FALSE),"")
G2:G71	G2	=IF(H2<>"",VLOOKUP(H2,'Data Base'!$B$2:$K$8201,10))
A2	A2	=AB2+AD2
A4	A4	=IF(H2<>"",IF(B2="",NOW(),B2),"")
A6	A6	=AB3-A10
A10	A10	=AB4
A14	A14	=AD3-A18
A18	A18	=AD4
J2:J71	J2	=IF(V2=TRUE,"In",0)

 

[Joe4]

OK, thank you for the data sample.
Now that we have that, can you provide an example of a number you are looking for?

So, if you run my macro, and enter "4", would you want cell H5 (that has the number 4) selected and highlighted?
Is that your expected behavior?

 

[Matt5353]

OK, thank you for the data sample.
Now that we have that, can you provide an example of a number you are looking for?

So, if you run my macro, and enter "4", would you want cell H5 (that has the number 4) selected and highlighted?
Is that your expected behavior?

Ys
If I select any number between 1 - 8200 it would select the cell, highlight it, go to the cell without having to scroll to find it.

 

[Joe4]

Ys
If I select any number between 1 - 8200 it would select the cell, highlight it, go to the cell without having to scroll to find it.

OK, that is exactly what the code I gave you will do. I tried it out, entering 67 in the input box that popped up, and it jumped to cell H68 (that contains the value 67) and highlighted it.
So it appears to be doing what you asked.

Did you follow the instuctions I included for how and where it insert the VBA code, and then for how to attach it to a button?
If you do that all correctly, then all you need to do is click the button, and it will prompt you to enter the number, and then when you click OK, it will jump to and highlight that number.

If that is not working for you, please tell us exactly what is happening.
If you get any error messages, please tell us exactly what they are.

 

[Matt5353]

I get an error in Search for value

Sub MyFind()

Dim x As Variant
Dim rng As Range

' Prompt user to enter value to look for
x = InputBox("What value do you want to find?")

' Search for value
Set rng = Cells.Find(What:=x, After:=Range("A1"), LookIn:=xlFormulas2, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False)

' Go to range
If rng Is Nothing Then
MsgBox "Cannot find " & x & " on the active sheet", vbOKOnly, "VALUE NOT FOUND!"
Else
' Select range
rng.Select
' Highlight range
rng.Interior.Color = 65535
End If

End Sub

 

[Joe4]

It may be because you are using an older version of Excel than I am.
Try replacing the "xlFormulas2" in that search section with "xlFormulas", and see if that makes any difference.

If it does not, then please do the following:
1. Turn on your Macro Recorder
2. Record yourself hitting CTRL+F on your sheet to bring up the "Find" box, enter the value you are looking for, and hit "Find Next".
3. Stop your Macro Recorder
4. Go into VBA, and copy and paste the code you just recorded here so I can see what it looks like.