Searching cells across workbooks and displaying workbook name

[mes_1986]

Hi,

Not actually sure if this is going to need VBA or can be solved with a simple excel formula, what I'm trying to achieve is:
I have 48 names in workbook 1, I then need to search 43 different workbooks to find out if that name shows up in any one or all of the 43 different workbooks. The name could be in any cell within the workbook, so I'm not able to limit to only searching in a set array cause the workbooks are all varying sizes.

The way I work it all out in my head is:
Look in cell A1 > search 43 workbooks > if cell A1 matches Workbook 1 display Workbook 1 name. If cell A1 matches Workbook 2 display Workbook 2 name (and so on)

Then repeat across A1 to A48 cells.

I usually don't go to badly at formulas in excel, but I'm so stumped on how to get what I need here, so any help would please be appreciated.

 

[Yard]

Hi, how do you know which 43 workbooks to search through? Is there a table of filenames and paths, or are they the only 43 files in a certain folder?

I'm pretty sure it's a VBA solution you need for this.

 

[mes_1986]

Hi, how do you know which 43 workbooks to search through? Is there a table of filenames and paths, or are they the only 43 files in a certain folder?

I'm pretty sure it's a VBA solution you need for this.

The file names wouldn’t change, when I do the activity I’d download all 43 files as point in time data then search through the match of the names on my list. All names would be a 100% match of what is in one or more workbooks.

many ideas on the vba, I can read it and make minor changes, but writing from scratch I wouldn’t have a clue

 

[Yard]

Yes, but in order for VBA to know which files to search, we need some rules.

Will the files always be in the same folder, and will they be the only files in that folder? And/or do you want to specify a list of those filenames and paths?

 

[Yard]

Try this for starters. Hopefully you can see where it gets the data from for the searches? Don't want to spend time explaining things you'll already get, but please let me know how I can help next!

Sub SearchForNames()
Dim shtFileSearch As Worksheet, shtToSearch As Worksheet
Dim wbkToOpen As Workbook
Dim strPath As String, strFilename As String, strFileToOpen As String, strToSearch As String, strReport As String
Dim rngFileList As Range, rngFile As Range
Dim rngNamesToSearch As Range, rngName As Range, rngToSearch As Range, rngFound As Range, rngToPaste As Range
Const strDelim As String = ", "

Set shtFileSearch = Worksheets("FileSearchData")
strPath = shtFileSearch.Range("FilePath").Value

Set rngFileList = shtFileSearch.Range("FileList")
Set rngNamesToSearch = shtFileSearch.Range("NamesList")

Set rngToPaste = shtFileSearch.Range("PasteStart")

For Each rngFile In rngFileList

    strFileToOpen = strPath & rngFile.Value
    Set wbkToOpen = Workbooks.Open(Filename:=strFileToOpen)
    
    For Each rngName In rngNamesToSearch
    
        strToSearch = ""
        strToSearch = rngName.Value
        If Len(strToSearch) > 0 Then
            For Each shtToSearch In wbkToOpen.Worksheets
            
                Set rngFound = Nothing
                Set rngFound = shtToSearch.UsedRange.Find(What:=strToSearch)
                If Not rngFound Is Nothing Then
                    strReport = strToSearch & strDelim & wbkToOpen.Name & strDelim & shtToSearch.Name & strDelim & rngFound.Address(ReferenceStyle:=xlA1)
                    rngToPaste.Value = strReport
                    Set rngToPaste = rngToPaste.Offset(1, 0)
                End If
            
            Next shtToSearch
        End If
    Next rngName

    wbkToOpen.Close
    
Next rngFile
End Sub

 

[Yard]

On reflection, some additional notes added to the code, below.

Also, note that I have assumed your list of files, list of names etc., are stored in named ranges on a single worksheet. You can edit these to suit your situation. I also don't know what you want to do with the results, so I just put some info about the results into a range on the same worksheet.

Sub SearchForNames()
'define all variables to be used in the code
Dim shtFileSearch As Worksheet, shtToSearch As Worksheet
Dim wbkToOpen As Workbook
Dim strPath As String, strFilename As String, strFileToOpen As String, strToSearch As String, strReport As String
Dim rngFileList As Range, rngFile As Range
Dim rngNamesToSearch As Range, rngName As Range, rngToSearch As Range, rngFound As Range, rngToPaste As Range
Const strDelim As String = ", "

'set which worksheet in this file to work with, assuming everything to do with this exercise is on the same sheet
Set shtFileSearch = Worksheets("FileSearchData")

'get the network path for the folder containing the files to be searched
strPath = shtFileSearch.Range("FilePath").Value

'define the range which contains the list of files
Set rngFileList = shtFileSearch.Range("FileList")
'define the range which contains the list of names to be sought
Set rngNamesToSearch = shtFileSearch.Range("NamesList")
'define the range where you want to start recording the results of the search (assuming this is the objective)
Set rngToPaste = shtFileSearch.Range("PasteStart")

'cycle through the files in the rngFileList
For Each rngFile In rngFileList
    'reset the workbook variable
    Set wbkToOpen = Nothing
    'test if the cell actually contains anything; if not, move to the next cell in the range
    If Len(rngFile.Value) = 0 Then Next rngFile
    'define the path+filename string
    strFileToOpen = strPath & rngFile.Value
    'open the workbook with the path+filename
    Set wbkToOpen = Workbooks.Open(Filename:=strFileToOpen)
    'if it didn't work (i.e. the file couldn't be opened), move to the next cell in the range
    If wbkToOpen Is Nothing Then Next rngFile
   
    'cycle through the names to be sought
    For Each rngName In rngNamesToSearch
   
        'reset the search string to empty
        strToSearch = ""
        'get the name to be sought from the cell we are cycling through
        strToSearch = rngName.Value
        'test if the cell contains anything
        If Len(strToSearch) > 0 Then
            'cycle through all the worksheets in the workbook being scoured
            For Each shtToSearch In wbkToOpen.Worksheets
           
                'reset the variable
                Set rngFound = Nothing
                'put the results of the Find into the variable
                Set rngFound = shtToSearch.UsedRange.Find(What:=strToSearch)
                'if it did find something, i.e. the variable now contains something, then...
                If Not rngFound Is Nothing Then
                    'do whatever you like here, I just recorded the found string, the workbook name, the worksheet name and the range where it was found
                    strReport = strToSearch & strDelim & wbkToOpen.Name & strDelim & shtToSearch.Name & strDelim & rngFound.Address(ReferenceStyle:=xlA1)
                    '...and put it into a cell on the worksheet...
                    rngToPaste.Value = strReport
                    '...then moved the reference to the next cell down for the next entry in the "found" list
                    Set rngToPaste = rngToPaste.Offset(1, 0)
                End If
           
            Next shtToSearch
        End If
    Next rngName
   
    'close the opened workbook once all the names have been sought in all of the worksheets in it
    wbkToOpen.Close
   
Next rngFile

End Sub

 

[mes_1986]

Thank you very much for this. Your assumption is correct in that the files will be in the one folder just for this activity. I will have a play with it to see if it works and what paths I may need.
I really appreciate the effort and time you’ve put in to solving this for me.