Search Entire Workbook

[ukdane]

Is it possible to search an entire workbook, for a string, (excluding the worksheet where the string originates), and return the name of the worksheet and the cell reference where it first occurs?

Cheers

[Bob Phillips]

Go to http://www.oaltd.co.uk/MVP/Default.htm and grab Jan Karel Pieterse's FlexFind utility.

[ukdane]

Thanks for the link. I've had a look, but I don't think it's quite what I need.

My code currently takes a number (which is a date in the format YYMMDD) and extracts the MM. It then searches the Worksheet with the same MM, for a string within that workbook. It all works fine.

But I've just been told by someone that the MM might be changed, which would cause the program to search a different Worksheet that the original worksheet, for the string. So I figured, if I could remove the "find MM", and just search the entire workbook for the string, then activate the cell where it appears.

I was hoping there was a "SEARCHWORKBOOK" solution

[lucas]

try this:
[VBA]
Option Explicit
Sub LineSearchTESTA123(Optional SearchVal As String)
Dim MyValue As String
Dim MyFindNext As Long
Dim sht As Worksheet
Dim Ans As Long
Dim FirstAddress As String
Dim Cel As Range
Dim Counter As Long
If SearchVal = "" Then
MyValue = InputBox("Company Name", "FAX / E-MAIL DATABASE")
Else
MyValue = SearchVal
End If
If MyValue = "" Then
[C3].Select
Exit Sub
End If
' For Each sht In Sheets(Array("A", "B", "C"))
For Each sht In ThisWorkbook.Sheets
With sht
sht.Activate
' Set Cel = .Columns(3).Find(What:=MyValue)
Set Cel = ActiveSheet.UsedRange.Find(What:=MyValue)
MyFindNext = vbYes
If Not Cel Is Nothing Then
FirstAddress = Cel.Address
Do
Counter = Counter + 1
Cel.Activate
MyFindNext = MsgBox("Next " & MyValue & "?", vbYesNo, "Find Next")
Set Cel = .UsedRange.FindNext(Cel)
Loop While Not Cel Is Nothing And _
Cel.Address <> FirstAddress And _
MyFindNext = vbYes
End If
End With
If MyFindNext = vbNo Then
Exit Sub
End If
Next
If Counter = 0 Then
Ans = MsgBox("Search could not find '" & MyValue & "'." & _
vbNewLine & " " & vbNewLine & _
"Try another search?", 4, MyValue & " not found")
' If Ans = vbYes Then
' Call LineSearchTESTA123(MyValue)
' Else
' Exit Sub
' End If
' End If
' If MyFindNext = vbYes Then
' Call LineSearchTESTA123(MyValue)
End If
' End If
End Sub
[/VBA]

[georgiboy]

This is what i came up with

[vba]Sub Find_From_Different_Sheet()
Dim wsItem As Worksheet
Dim ToFind As String

'Whatever you want to find
ToFind = "Whatever you want to find"

'loop through sheets
For Each wsItem In ThisWorkbook.Sheets
'to look at all sheets other than active sheet
If wsItem.Name <> ActiveSheet.Name Then
'to make sure there is a match on sheet
If WorksheetFunction.CountIf(wsItem.Cells, ToFind) > 0 Then
'display found cell and sheet foumd in
MsgBox wsItem.Cells.Find(What:=ToFind, After:=wsItem.Range("A1")).Address & vbNewLine & wsItem.Name
'optional part to select next instance then exit for
'wsItem.Select
'Range(wsItem.Cells.Find(What:=ToFind, After:=wsItem.Range("A1")).Address).Select
'Exit For
End If
End If

Next wsItem

End Sub[/vba]

Hope this helps