Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return position for each matching value in entire workbook?
Is there a function or command for a cell in a worksheet that results in a
search of the entire Excel workbook and which then returns (in the worksheet) the full value / content of each cell in the workbook that matches the criteria of the search. For example, on one worksheet I want to see every cell (in its entirety) of the workbook that contains a particular number or phrase. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Return position for each matching value in entire workbook?
You don't say how many typical finds you expect. In extremis, if you
want to list all matches on a single sheet then you could record 65536 times 256, matches. What happens if the rest of the 255 sheets contain more matches than can be recorded on one sheet. However if your matches are likely to be more modest, no more than 65535 then the following macro will list the sheet name and the cell addresses in a sheet called "List" Watch the word wrap in the line beginning "Worksheets("List")", it's all one line and ends with "& stAddress" The value this finds is a string called "test". See the first line of code. You'll probably want to modify the macro with an input box so that you can easily change this at runtime. HTH Sub MyFind() Dim stFirstAddress As String, stAddress As String Dim vaFind As Variant Dim iSheets As Integer Dim x As Integer vaFind = "test" iSheets = ActiveWorkbook.Sheets.Count For x = 1 To iSheets If Worksheets(x).Name < "List" Then Worksheets(x).Activate On Error Resume Next Cells.Select stFirstAddress = Selection.Find(What:=vaFind, After:=Range("A1")).Address Selection.Find(What:=vaFind, After:=ActiveCell).Activate Do Until stAddress = stFirstAddress stAddress = ActiveCell.Address Worksheets("List").Range("A65536").End(xlUp).Offse t(1, 0) = Worksheets(x).Name & " " & stAddress Selection.Find(What:=vaFind, After:=ActiveCell).Activate stAddress = ActiveCell.Address Loop End If Next End Sub On Fri, 15 Sep 2006 11:42:01 -0700, Squidman wrote: Is there a function or command for a cell in a worksheet that results in a search of the entire Excel workbook and which then returns (in the worksheet) the full value / content of each cell in the workbook that matches the criteria of the search. For example, on one worksheet I want to see every cell (in its entirety) of the workbook that contains a particular number or phrase. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return all matching values | Excel Worksheet Functions | |||
Set 'Print Entire Workbook' as default? | Excel Discussion (Misc queries) | |||
Saving an entire workbook as a PDF using VBSCript | Excel Worksheet Functions | |||
printing entire workbook | Excel Discussion (Misc queries) | |||
print entire workbook default | Excel Discussion (Misc queries) |