Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel: 'Find All' macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel: 'Find All' macro
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 --------------- Jacob Skaria "bawpie" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel: 'Find All' macro
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) On Fri, 25 Sep 2009 03:29:02 -0700, bawpie wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use 'Find All' and copy the entire row | Excel Discussion (Misc queries) | |||
Macro, or excel program to "Close All' while in a workspace | Excel Programming | |||
Macro, or excel program to "Close All' while in a workspace | Excel Programming | |||
How do I insert a 'clear all' button in excel? | Excel Worksheet Functions | |||
Data Filtering 'Show All' macro | Excel Programming |