Macro for finding cells

**_nic_v** · 02-09-2005, 07:02 AM

Hi there I'm new to this forum but wondered if anyone could help me!? I have a workbook which has severalsheets in, on my main menu page I would like a macro so that when the user clicks a button the 'Find' window pops up (for the lazy people who don't know to use Ctrl+F). Also when searching for data it needs to search every sheet in the workbook. At the moment when i try to record the macro it doesn't work - simply because when the find window appears i then have to close it before stopping the macro from recording - so when the macro runs it doesn't actually appear! Can anyone help?
Thanks

**funkymonkUK** · 02-09-2005, 07:31 AM

do you want a custom find box by which i mean if you want some thing to pop up and say good morning what do you want to find??? you type it in it searchs and then takes you to it??

**_nic_v** · 02-09-2005, 07:41 AM

hi there, I'd like the user to enter a persons surname into the find box, or the title of a course and it takes them to the appropriate cell - this could be in any one of seven worksheets. So yes a custom find box would be ideal but didn't know how to do it in Excel or if it was even possible - VB isn't my strongest point!

**funkymonkUK** · 02-09-2005, 11:10 AM

Originally Posted by _nic_v

hi there, I'd like the user to enter a persons surname into the find box, or the title of a course and it takes them to the appropriate cell - this could be in any one of seven worksheets. So yes a custom find box would be ideal but didn't know how to do it in Excel or if it was even possible - VB isn't my strongest point!

i take it you know how to create a button. then if you add this code to a module

Sub test()
' inbetween the "" part change to what ever you want it to say
y = InputBox("What are you looking for?")

For x = 1 To ActiveWorkbook.Sheets.Count ' this counts the sheets and repeats the below as many times as there are sheets

Sheets(x).Activate

'the below part searches for what the user typed in

Set abc = Cells.Find(What:=y, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False)

Next x
If abc Is Nothing Then
' If PC can't find value it tells you it can not
' in between the "" part change to what ever you want it to say
MsgBox "Sorry I could not find what you want!"

Else
'if it does find it will take you to it
abc.Activate
End If
End Sub

Hope this helps if you have any trouble with it let me know and yeah please let me know if it is exactly what you wanted?

**_nic_v** · 02-09-2005, 11:37 AM

Well it sort of works - I can see how it would work but every thing I search for brings back that it couldn't find what I'm looking for, even though I'm testing it with surnames which I know are definately in the worksheets. If it makes any difference then the only column that needs to be searched is A as this contains people's surnames (which is what I would like to search for).

Also, don't know if this is possible, but in some instances there may be more than one person with that surname but their records may be on different sheets - how would the user know that there are (for example) 3 people to view.

Sorry to be a pain but I wouldn't know where to start with something like this!