Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search/Find in any worksheet
Hello,
Is it possible to search for a string in all the worksheets of a workbook and not just the active worksheet? If not, is it possible to make a macro to do so (could someone give me a hand)? Thank you Daniel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search/Find in any worksheet
Daniel,
The sub bleow will find all complete matches of the entered string in the active workbook. HTH, Bernie MS Excel MVP Sub FindAllValuesInWorkbook() Dim c As Range ' The cell found with what you want Dim d As Range ' All the cells found with what you want Dim myFindString As String Dim firstAddress As String Dim mySht As Worksheet myFindString = InputBox("Enter the key word for finding", _ , "What to find") For Each mySht In ActiveWorkbook.Worksheets With mySht.Cells Set c = .Find(myFindString, LookIn:=xlValues, lookAt:=xlWhole) If Not c Is Nothing Then Set d = c firstAddress = c.Address Else: MsgBox "Not Found" GoTo NotFound: End If Set c = .FindNext(c) If Not c Is Nothing And c.Address < firstAddress Then Do Set d = Union(d, c) Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address < firstAddress End If End With 'Then do what you want with all 'the cells that have been found, like MsgBox "On sheet " & mySht.Name & ", found in " & d.Address NotFound: Next mySht End Sub "Daniel" wrote in message ... Hello, Is it possible to search for a string in all the worksheets of a workbook and not just the active worksheet? If not, is it possible to make a macro to do so (could someone give me a hand)? Thank you Daniel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Search/Find in any worksheet
Hi
try Jan Karel Pieterse's FLEXFIND.XLA from http://www.bmsltd.ie/mvp/ -- Regards Frank Kabel Frankfurt, Germany Daniel wrote: Hello, Is it possible to search for a string in all the worksheets of a workbook and not just the active worksheet? If not, is it possible to make a macro to do so (could someone give me a hand)? Thank you Daniel |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search / Find Function | Excel Worksheet Functions | |||
Search one worksheet to pull data into another worksheet | Excel Worksheet Functions | |||
IF with Search/Find??? | Excel Worksheet Functions | |||
Can Search find 2 or more "/"? | Excel Discussion (Misc queries) | |||
Search and find | Excel Programming |