Excel - Build In CTRL F to worksheet

  User-67C15525-3DBE-41EF-B48A981FE25748D2 20:19 01 Dec 2007

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?


  VoG II 22:16 01 Dec 2007

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 2007

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
Set Found = tempCell
Set FoundRange = Found
End If
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)
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 2009

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 ?


  VoG II 18:42 12 Mar 2009

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 2009

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.

Elsewhere on IDG sites

Dell XPS 13 9370 (2018) review

No need to scan sketches into your computer with Moleskine's new smart pen

HomePod review

Streaming : Netflix ou Amazon Prime Video ?