find a value across an array of worksheets

[Jay]

Hi there,

Pretty new to vba and having some difficulties.

I want the user to able to select mulitple files to open but then have Excel
automatically search through the workbooks for a specific value.

Each worksheet may or may not contain the value. The value will be part of
a longer string and will be on any given sheet within the workbook, the
number of sheets in each workbook can vary from 1 to over a 1000.

Here's what I've got so far,


Private Sub SelectFiles()

FilesToOpen = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls),*.xls", MultiSelect:=True)
If Not IsArray(FilesToOpen) Then
MsgBox "Nothing selected"
Else
For Book = LBound(FilesToOpen) To UBound(FilesToOpen)
Workbooks.Open Filename:=FilesToOpen(Book)
Call CodeSearch
Next
End If

End Sub

Private Sub CodeSearch()

Sheets("Sheet276").Select
Cells.Find(What:="60-2300", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

End Sub

I recorded the cells.find bit as a macro, problem is I'll have no idea which
sheet my value is on. I've tried a bunch of things and am stuck.

Any ideas?

Thanks

 

[Don Guillett]

Sub FindNumberInSheets()
For Each ws In Worksheets
'MsgBox ws.Name
With ws.Cells
Set x = .Find("60-2300", LookIn:=xlValues)
End With
If Not x Is Nothing Then
MsgBox ws.Name & " " & x.Address
Application.Goto Sheets(ws.Name).Range(x.Address)
Exit For
Exit For
End If
Next ws
End Sub

 

[meh2030]

Hi there,

Pretty new to vba and having some difficulties.  

I want the user to able to select mulitple files to open but then have Excel
automatically search through the workbooks for a specific value.  

Each worksheet may or may not contain the value.  The value will be part of
a longer string and will be on any given sheet within the workbook, the
number of sheets in each workbook can vary from 1 to over a 1000.

Here's what I've got so far,

Private Sub SelectFiles()

FilesToOpen = Application.GetOpenFilename(filefilter:="Excel Files
(*.xls),*.xls", MultiSelect:=True)
If Not IsArray(FilesToOpen) Then
    MsgBox "Nothing selected"
Else
    For Book = LBound(FilesToOpen) To UBound(FilesToOpen)
        Workbooks.Open Filename:=FilesToOpen(Book)
        Call CodeSearch
    Next
End If

End Sub

Private Sub CodeSearch()

Sheets("Sheet276").Select
    Cells.Find(What:="60-2300", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

End Sub

I recorded the cells.find bit as a macro, problem is I'll have no idea which
sheet my value is on.  I've tried a bunch of things and am stuck.

Any ideas?

Thanks

Jay,

Are you simply looking for the first instance of your search item?
The code below should find the instance, but I'm not sure what else
you want to do with this. (Note: I have not tested the code). As a
side note, it's good programming practice to dimension all of your
variables (i.e. to use Option Explicit: Tools | Options; Editor page,
"Require Variable Declaration").

Best,

Matthew Herbert

Private Sub SelectFiles()

Dim varArrFilesToOpen As Variant
Dim lngBook As Long
Dim Wkb As Workbook
Dim rngSheet As Range

varArrFilesToOpen = Application.GetOpenFilename(filefilter:="Excel
Files(*.xls),*.xls", MultiSelect:=True)

If Not IsArray(varArrFilesToOpen) Then
MsgBox "Nothing selected"

Else

For lngBook = LBound(varArrFilesToOpen) To UBound
(varArrFilesToOpen)
Set Wkb = Workbooks.Open(Filename:=varArrFilesToOpen(lngBook))

Set rngSheet = CodeSearch(Wkb, "60-2300")

If rngSheet Is Nothing Then
MsgBox "Didn't find it on " & varArrFilesToOpen(lngBook)

Else

MsgBox "Found it at the following address: " &
rngSheet.Address(External:=True)
End If
Next
End If

End Sub

Private Function CodeSearch(Wkb As Workbook, varFindWhat As Variant)
As Range

Dim rngSearch As Range
Dim rngLastCell As Range
Dim rngFound As Range
Dim Wks As Worksheet

'loop through each worksheet in Wkb to search for
' varFindWhat
For Each Wks In Wkb.Worksheets

'set the search range
Set rngSearch = Wks.Cells

'get the last cell in the search range
With rngSearch
Set rngLastCell = .Cells(.Cells.Count)
End With

'see "Remarks" in Find Method help documentation for choosen
' arguments in the Find Method
Set rngFound = rngSearch.Find(What:=varFindWhat, _
After:=rngLastCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows)

If Not rngFound Is Nothing Then
Set CodeSearch = rngFound
Exit Function
End If
Next

If rngFound Is Nothing Then
Set CodeSearch = Nothing
End If

End Function