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.

jegajothy

Asked: 2014-02-02

Advertise Here

Microsoft Excel

2 Solutions

LVL 18

Steven HarrisPresidentCommented: 2014-02-02

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

LVL 18

Steven HarrisPresidentCommented: 2014-02-02

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