Solved

Excel 2007 search

Posted on 2014-02-02
3
239 Views
Last Modified: 2014-02-06
My OS is win 7 prof 64 bit and I use Excel 2007.  I have an Excel file which has many sheets.  I would like to enquire if it is possible to do a search across all the sheets that is in this file, or does this need a macro.
I would like to search  for the word "Nokia", across all the sheets to see in which sheet, col and row it is in.  Thank u.
0
Comment
Question by:jegajothy
  • 2
3 Comments
 
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 500 total points
ID: 39827674
If this is a one time operation and you don't need the physical address displayed:

1) Right-Click a sheet's tab and choose Select All; or
Ctrl+click all sheets

2) Use Ctrl+f to do a normal search

3) Use Find All instead of Find Next

sheet...name...value...
0
 
LVL 18

Accepted Solution

by:
Steven Harris earned 500 total points
ID: 39827694
If you would rather have a macro:

You can use:

Sub FindText()

Dim ws As Worksheet, Found As Range, rngNm As String
Dim fText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer

fText = InputBox("Enter the text that you want to search for:", "Start Search!")

If fText = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=fText, LookIn:=xlValues, MatchCase:=False)

If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address

Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select

myFind = MsgBox("I have located your text """ & fText & """ in cell!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel + vbDefaultButton1, "Search Result!")

If myFind = 2 Then Exit Sub

Set Found = .UsedRange.FindNext(Found)

Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With

Next ws

If Len(AddressStr) Then
MsgBox "Found: """ & fText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, fText & " - was found in these cells only"
Else:
MsgBox "Unable to find your text '" & fText & "' in this workbook.", vbExclamation, "Search Completed!"

End If

End Sub

Open in new window


Which uses a popup box to display each location, and then a final result popup to list all locations.
0
 

Author Closing Comment

by:jegajothy
ID: 39840254
Thank u for both solutions.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

825 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question