Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 264
  • Last Modified:

Excel 2007 search

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
jegajothy
Asked:
jegajothy
  • 2
2 Solutions
 
Steven HarrisPresidentCommented:
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
 
Steven HarrisPresidentCommented:
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
 
jegajothyretiredAuthor Commented:
Thank u for both solutions.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now