Excel Search Query

D'Agger

Hi all,

I rarely use excel due to my job not requiring it much, however I've had to get actively involved in improving our documentation and I have a query regarding Excel, and Excel VBA in particular.

I'm using office 2003 & I need to search a phone directory that has columns B: Frist Name, C: Last name & Extension Number

Something that has been requested, due to certain users not being tech savvy is a search button.

I've been running through ways to do this but I can't find anything that can get the search working the way I want - which is the as follows:

- Search for the string entered, first name or last name
- Highlight the row containing the information
- If that isn't the correct user, a 'find next' button or ability to hit enter and allow it to highlight the next user.

So essentially if theres two users D'Agger A & D'Agger B, and I enter 'D'Agg' into the searchbox, then I should have D'Agger A & his extension highlighted, I realize that's not the person I'm looking for, I hit enter and D'Agger B has his information highlighted.

I'm continuing to look through youtube tutorials and excel sites but everything I'm coming across highlights all rows and leaves the info I'm looking for unhighlighted, looks to change font colour & bugs out - most tweaks I'm making to the VBA code aren't helping unfortunately.

Would appreciate some insight!

Thoie

Try this and see how you get on. The only other piece to add is to put a button where you want it, and link the macro to that button, which is easily googleable

Sub FindSomething()


' Pop up box to enter search term
WhatToFind = InputBox("Please enter search term")

'Search routine to find whatever was entered
Cells.Find(What:=WhatToFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
' As long as the user keeps replying Yes to Find Next, keep running the search.  Stop when they say No.
Do While True       ' Start Loop
    Response = MsgBox("Find Next?", vbYesNo)         ' Message box saying Find Next?
    Cells.Find(What:=WhatToFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    If Response = vbNo Then         ' Check if the user has said no yet
        Exit Do
    End If
Loop                ' End Loop

End Sub

Thoie

Just realised you wanted highlighting - will have to think about that one

Would it be enough to just highlight the piece it found (e.g. First Name) in yellow, not the extension as well? As just highlighting the name is easier.

beauf

I would have thought something like access would be better for this. Returning just the names in the search.

Jumping through them as described seems a tad awkward.

D'Agger

Thoie wrote: »

Just realised you wanted highlighting - will have to think about that one

Would it be enough to just highlight the piece it found (e.g. First Name) in yellow, not the extension as well? As just highlighting the name is easier.

I'll take what help I can get, I won't push it

Thanks for that, will give it a lash later this afternoon

beauf wrote: »

I would have thought something like access would be better for this. Returning just the names in the search.

Jumping through them as described seems a tad awkward.

Thing is, we have an excel sheet for the extension list.

An Access DB with the info would be far easier to setup but for use by general users - they'd need access to open the DB wouldn't they?

beauf

Need Access Yes they would. If they only have Excel I guess you've no choice.

Doesn't the find dialog in Excel do this though. Search for a word and give you a next button?

D'Agger

beauf wrote: »

Need Access Yes they would. If they only have Excel I guess you've no choice.

Doesn't the find dialog in Excel do this though. Search for a word and give you a next button?

Oh it does, I've just been asked to put in a search function for users who're unable to hit CTRL + F : normally I'd give out but I suppose this kind of stuff is why I work in IT - there's always work to be found :pac:

beauf

Put in a button that gives a message, click the friggin CTRL + F button. They'll soon learn.

http://www.mrexcel.com/forum/excel-questions/71530-visual-basic-applications-find-dialog-box.html

gctest50

http://www.artlebedev.com/everything/optimus/mini-three/presentation/

sold out now though

Stheno

beauf wrote: »

Need Access Yes they would. If they only have Excel I guess you've no choice.

Doesn't the find dialog in Excel do this though. Search for a word and give you a next button?

I was just thinking this

D'Agger wrote: »

Oh it does, I've just been asked to put in a search function for users who're unable to hit CTRL + F : normally I'd give out but I suppose this kind of stuff is why I work in IT - there's always work to be found :pac:

beauf wrote: »

Put in a button that gives a message, click the friggin CTRL + F button. They'll soon learn.

http://www.mrexcel.com/forum/excel-questions/71530-visual-basic-applications-find-dialog-box.html

Yes don't complicate it any more, these are users D'Agger

You not working too long in IT?

Thoie

D'Agger wrote: »

I'll take what help I can get, I won't push it

Thanks for that, will give it a lash later this afternoon

How did you get on? Because I'm a nasty person, I'd probably change the "Enter the search term" dialogue box to say something like "Enter the search term, but next time press the giant "Find" button up above"

I've done some "colouring in", but it's not beautiful by any means, and has the potential to leave the sheet covered in yellow cells if things go wrong. On the other hand, I'm not charging you my hourly rate.

Sub FindSomething()

' NOW IN TECHNICOLOR


' Pop up box to enter search term
WhatToFind = InputBox("Please enter search term")

'Search routine to find whatever was entered
Cells.Find(What:=WhatToFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
        
' As long as the user keeps replying Yes to Find Next, keep running the search.  Stop when they say No.
Do While True       ' Start Loop
    Response = MsgBox("Find Next?", vbYesNo)         ' Message box saying Find Next?
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Cells.Find(What:=WhatToFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    If Response = vbNo Then         ' Check if the user has said no yet
        Exit Do
    End If
Loop                ' End Loop

End Sub

D'Agger

Stheno wrote: »

I was just thinking this

Yes don't complicate it any more, these are users D'Agger

You not working too long in IT?

Working in IT with four years - I'm fully aware that this is unnecessary and a simple CTRL + F is what's needed but this is being requested of me by management so, despite my obviously warranted protestations, the search button goes

D'Agger

Thoie wrote: »

How did you get on? Because I'm a nasty person, I'd probably change the "Enter the search term" dialogue box to say something like "Enter the search term, but next time press the giant "Find" button up above"

I've done some "colouring in", but it's not beautiful by any means, and has the potential to leave the sheet covered in yellow cells if things go wrong. On the other hand, I'm not charging you my hourly rate.

Sub FindSomething()

' NOW IN TECHNICOLOR


' Pop up box to enter search term
WhatToFind = InputBox("Please enter search term")

'Search routine to find whatever was entered
Cells.Find(What:=WhatToFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
End With
        
' As long as the user keeps replying Yes to Find Next, keep running the search.  Stop when they say No.
Do While True       ' Start Loop
    Response = MsgBox("Find Next?", vbYesNo)         ' Message box saying Find Next?
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    
    Cells.Find(What:=WhatToFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    If Response = vbNo Then         ' Check if the user has said no yet
        Exit Do
    End If
Loop                ' End Loop

End Sub

The initial macro works really well thanks a million Thoie

The second seemed to work too but left the cells with the background fill.

Either way, I'll use the initial one, forward it on with the note added - 'feel free to use the CTRL + F, or the search button....which took longer to figure out than the rest of the entire phone directory' - I will of course not take credit for the macro, they'll know if I lied!! :pac:

Thoie

D'Agger wrote: »

Working in IT with four years - I'm fully aware that this is unnecessary and a simple CTRL + F is what's needed but this is being requested of me by management so, despite my obviously warranted protestations, the search button goes

Every now and again you could swap the function behind the button to this:

Sub FindIT()
URL = "https://www.youtube.com/watch?v=iTraG7F2_FY"
ActiveWorkbook.FollowHyperlink Address:=URL, NewWindow:=True
End Sub

Then switch it back again. Even better, put an if statement into the macro for something that appears random - e.g. if the Mod13 of the minute of the current time stamp is 0, then play the youtube video, otherwise continue with the "normal" search.

That gives the opportunity for the video to be played 4 times an hour, but the reality is someone will only hit the right minute every few days, at best (depending on the number of users). The likelihood of someone figuring out that the video plays if I start searching at 39 minutes past the hour is low.