Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
find a value across an array of worksheets
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
find a value across an array of worksheets
On Apr 28, 4:01*pm, Jay wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
Looped Find and Array Doesn't Find | Excel Programming | |||
Big Array from several worksheets | Excel Programming | |||
find each of the items in an array and save result in another array | Excel Programming | |||
Two worksheets, one array | Excel Discussion (Misc queries) |