Excel - Build In CTRL F to worksheet

Instead of selecting CTRL + F and getting the search box to pop up, is there a way to build a search box into one of the cells so i can type directly into a cell to search?

can anyone help?

tia.

  VoG II 22:16 01 Dec 07

Do you mean that if you type something into B2 (for example) that a search should be performed for whatever is in B2? Should the search stop there or should all matches with B2 be found? How should these matches be indicated - different fill colour etc.?

  VoG II 22:45 01 Dec 07

OK well I can't wait forever so press ALT + F11 to open the Visual Basic Editor. Insert > Module. Copy and paste in

Dim FoundRange As Range

Sub FindHighlight()
Dim tempCell As Range, Found As Range, sTxt As String
sTxt = InputBox("Search string")
If sTxt = "False" Then Exit Sub
Set Found = Range("A1")
Set tempCell = Cells.Find(what:=sTxt, After:=Found, SearchDirection:=xlNext, MatchCase:=False)
If tempCell Is Nothing Then
MsgBox prompt:="Not found", Title:="Finder"
Exit Sub
Else
Set Found = tempCell
Set FoundRange = Found
End If
Do
Set tempCell = Cells.FindNext(After:=Found)
If Found.Row >= tempCell.Row And Found.Column >= tempCell.Column Then Exit Do
Set Found = tempCell
Set FoundRange = Application.Union(FoundRange, Found)
Loop
FoundRange.Interior.ColorIndex = 6
FoundRange.Font.ColorIndex = 3
End Sub

Sub ClearHighlight()
FoundRange.Interior.ColorIndex = xlNone
FoundRange.Font.ColorIndex = xlAutomatic
End Sub


Close the VBE. Tools > Macro > Macros, click on FindHighlight and click the Run button. To undo the highlighting follow the same but run ClearHighlight (within the same Excel session).

  gunner1999 20:34 09 Mar 09

Hi Vog,
I have tried this macro and for the most part it is great but it freezes completely when i search for some numbers on my spreadsheet.
I started a fresh spreadsheet and it did the same in that especially when i search for 116500.

Any Ideas why ?

Thanks

  VoG II 18:42 12 Mar 09

I'm sorry but I don't know why that is happening. I've just tried searching a sheet with multiple occurrences of 116500 amongst thousands of random numbers and it finds them all 'instantly'.

Does Excel's built-in search work OK?

  gunner1999 08:55 13 Mar 09

Hi VoG,
Thanks for responding.
I have since found that it is nothing to do with the number being 116500 as it does it with other numbers.
I have a spreadsheet which is basically a plan of a warehouse and the cells relate to rows of pallets.
I use your excellent module above to find where these pallets are quickly without wandering roud the place searching for them.
On the whole it works great but some searches seem to cause a runtime error and others cause the program to freeze (task manager shows excel running 50% cpu during this time but does not respond)most of the cells are normal but some are merged either horizontally or vertically (vertical ones have the text alignment running top to bottom).
Is there anyway I could send you a copy of the spreadsheet listing the numbers that are giving problems so you could have a look at it?

Again Many thanks

This thread is now locked and can not be replied to.

Intel Coffee Lake 8th-gen Core processors release date rumours

1995-2015: How technology has changed the world in 20 years

Framestore’s haunting post-WWII title sequence for new BBC series SS-GB

Best iPhone games 2017 | Best iPad games 2017: 162 fantastic iOS games that you need to play right…