Excel: 'Find All' macro

[bawpie]

Hi,

I'm setting up a contact directory spreadsheet at the request of a
colleague, and it comprises of multiple sheets. I'm aware that I can simply
tell the user to select all of the worksheets and click c+F and the find all
option and it'll look for what they want, but I was wondering if it would be
possible to incorporate this as a macro, so the user needs only click one
button and type what they want to find in simply because I'm aware this
spreadsheet might be used by those with very little excel experience. I'd
like to simply automate the first few steps of the process i.e. 'select all
sheets', open find window, check 'find all' and then the user can simply type
in what they're looking for and click search.

I've tried recording it as a macro but the macro recorder doesn't pick up
any actions conducted in the find window, so any help on this would be much
appreciated!

Thankyou in advance,

Kirsty

 

[Jacob Skaria]

Try the below..will certainly take you in the right direction

Sub Macro()
Dim varFound As Variant, varSearch As Variant
Dim strAddress As String, ws As Worksheet

varSearch = InputBox("Search string")
For Each ws In ActiveWorkbook.Sheets
ws.UsedRange.Interior.ColorIndex = 0
ws.UsedRange.Font.ColorIndex = 0
With ws.UsedRange
Set varFound = .Find(varSearch, LookIn:=xlValues)
If Not varFound Is Nothing Then
strAddress = varFound.Address
Do
varFound.Interior.ColorIndex = 3
varFound.Font.ColorIndex = 4
Set varFound = .FindNext(varFound)
Loop While Not varFound Is Nothing And _
varFound.Address <> strAddress
End If
End With
Next
End Sub

If this post helps click Yes

 

[Chip Pearson]

You might be interested in my FindAll add-in. It allows you to select
any one or more worksheets in the active workbook, specify the various
search parameters, and it will search the ranges and sheets for a
value and display a list of the address of the found cells along with
the contents of those cells.

See http://www.cpearson.com/excel/FindAllXLA.aspx for details and
download.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)