finding a value using vba

  • Thread starter Thread starter tom
  • Start date Start date
T

tom

Hi,

How can I find a certain value, say x, in all the sheets
in a workbook or excel file using VBA? After finding it,
I must output all the sheet names that has x and its cell
location on a msgbox.

Thanks.
 
tom wrote...
How can I find a certain value, say x, in all the sheets in a
workbook or excel file using VBA? After finding it, I must output
all the sheet names that has x and its cell location on a msgbox.
Click to expand...

Something like


Sub foo()
Dim rv As String, ffa As String, ss As Variant
Dim ws As Worksheet, c As Range

ss = Application.InputBox( _
Prompt:="Enter string to find:", _
Title:="Search & List", _
Type:=2 _
)

If ss = False Then Exit Sub

For Each ws In ActiveWorkbook.Worksheets
Set c = ws.UsedRange.Cells(ws.UsedRange.Cells.Count)

On Error Resume Next

Set c = ws.UsedRange.Find( _
What:=ss, _
After:=c, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchDirection:=xlNext, _
MatchCase:=True _
)

If Err.Number = 0 And Not c Is Nothing Then
ffa = c.Address(0, 0)

Do
rv = rv & Chr(13) & c.Address(0, 0, xlA1, 1)

Set c = ws.UsedRange.Find( _
What:=ss, _
After:=c, _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchDirection:=xlNext, _
MatchCase:=True _
)

Loop While c.Address(0, 0) <> ffa

Else
Err.Clear

End If

Next ws


If rv <> "" Then
MsgBox _
Prompt:="Found the text '" & ss & "' in" & rv, _
Title:="Search & List"

Else
MsgBox _
Prompt:="No cells contain '" & ss & "'", _
Title:="Search & List"

End If

End Sub
 
Back
Top