0

How can we create a search function on excel? I want to be able to put a word in a search box and the rows containing it should be 'selected'.

I have this : 

Sub find_highlight() 

w = InputBox("What to find?") 

Cells.Find(What:=(w), After:=ActiveCell, LookIn:=xlFormulas, _ 
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
MatchCase:=False).Activate 
 'Rows("51:51").Select
With Selection.Interior 
    .ColorIndex = 6 
    .Pattern = xlSolid 
    .PatternColorIndex = xlAutomatic 
End With 
End Sub

This only highlights one word and only one. I want all of the rows containing that word to be selected.

M--
  • 25,431
  • 8
  • 61
  • 93
  • Us conditional formatting, Clear and create the rule each time it is run, or you will need to loop through the range using find highlighting each row. Find only finds the first. – Scott Craner Apr 05 '17 at 14:13
  • I don't want to highlight, I want to 'select' the row –  Apr 05 '17 at 14:13
  • 1
    Look up `.FindNext`. –  Apr 05 '17 at 14:15
  • 1
    Then put the row in a unionized range and select after looping through the whole range. – Scott Craner Apr 05 '17 at 14:15
  • how do i do that ? –  Apr 05 '17 at 14:15
  • 1
    Look up `.FindNext`. –  Apr 05 '17 at 14:16
  • You mean you want the row to be highlighted every time a word is found in that row? You don't actually need them to be selected, correct? Why don't you want to use Conditional Formatting? – BruceWayne Apr 05 '17 at 14:16
  • I want the row to be selected because after the search, i want the document to be the same as the original. I want a search box to help me find data more easily –  Apr 05 '17 at 14:17
  • 2
    Look up `.FindNext`. –  Apr 05 '17 at 14:18
  • After you lookup what @Jeeped has suggested three times, look up `Union` and it will help you create a unionized range. – Scott Craner Apr 05 '17 at 14:21
  • Sorry, why do you need the rows to be selected? I'm asking because it's best to [avoid using `.Select`/`.Activate`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). Is the word you're looking for in *any* column, or is it always in the same one or two? – BruceWayne Apr 05 '17 at 14:24
  • do something like applying a filter with the value selecting the cells and removing the filter – Sivaprasath Vadivel Apr 05 '17 at 14:25
  • cells.autofilter field:=1, criteria:=w cells.specialcells(xlcelltypevisible).select cells.autofilter – Sivaprasath Vadivel Apr 05 '17 at 14:26
  • My document has a lot of repetitve words. I do not necessarily need the row to be selected but I have to be able to iterate through all of them. Like a next button on which ou can click until you see the one you want. but it seems too complex for me to write that –  Apr 05 '17 at 14:34
  • like the same thing as control F but with a button –  Apr 05 '17 at 14:35
  • @ScottCraner thank you it worked –  Apr 05 '17 at 14:59

0 Answers0