J
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
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