search xl file w/vba
 

i am looking to enter a search term, look in the entire workbook for the
term, then return the sheet name and row number of all found instances. wild
card searches would be a bonus if possible. tried a few things but nothing
works quite right.

search xl file w/vba
 

Did you try going to File=Open, then in the upper right of the dialog,
select tools and then Search?

--
Regards,
Tom Ogilvy

"inov8r" wrote:

i am looking to enter a search term, look in the entire workbook for the
term, then return the sheet name and row number of all found instances. wild
card searches would be a bonus if possible. tried a few things but nothing
works quite right.

search xl file w/vba
 

Hi,

Search the VBA help for the Find Method to see an example. Here's one way
doing it. It will search for "Hello" in the workbook and concatenate the
addresses in a string variable.

Sub FindHello()
Dim sht As Worksheet
Dim c As Range
Dim strFindResults
Dim firstAddress As String
For Each sht In ActiveWorkbook.Worksheets
With sht.UsedRange
Set c = .Find("Hello", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
strFindResults = strFindResults & "'" & c.Parent.Name & "'!"
& c.Address & vbNewLine
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
Next sht
MsgBox strFindResults

End Sub


--
Hope that helps.

Vergel Adriano


"inov8r" wrote:

i am looking to enter a search term, look in the entire workbook for the
term, then return the sheet name and row number of all found instances. wild
card searches would be a bonus if possible. tried a few things but nothing
works quite right.

search xl file w/vba
 

Never mind, I misread you posting.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:

Did you try going to File=Open, then in the upper right of the dialog,
select tools and then Search?

--
Regards,
Tom Ogilvy

"inov8r" wrote:

i am looking to enter a search term, look in the entire workbook for the
term, then return the sheet name and row number of all found instances. wild
card searches would be a bonus if possible. tried a few things but nothing
works quite right.