Solved

Excel 2007 search

Posted on 2014-02-02
3
241 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

733 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