search across all open workbooks for user defined criteria Solved/Closed

Question

Here is my problem - we store info into files that use an index number (changes with each file created) plus 2 criteria to keep track as we produce material. There is a 3rd criteria listed in the files that I'm looking for and it will always be in the same range (C21:C26 on sheet1 - each workbook opened will only have 1 sheet), but I just don't know on which file it will be located and we have thousands of files to search through. I've written this code to open all files matching the 2 known criteria:

Sub findcert()

' Opens all workbooks with partial match from input box so far
' This is step one
'
    Dim MyFolder As String
    Dim MyFile As String
    
    'size of wire
    filename1 = InputBox("enter size", _
    Title:="Size needed", Default:="IW")
    
    'heat number
    filename2 = InputBox("enter heat number", _
    Title:="Heat number needed", Default:="999")
    
    MyFolder = "Y:\FLO\ITSW\Quality Assurance (8200)\02 - Documentation\01 - Certificates\"
    MyFile = Dir(MyFolder & "\*" & filename1 & "*" & filename2 & "*.xlsx")
    Do Until MyFile = ""
        Workbooks.Open Filename:=MyFolder & "" & MyFile
        MyFile = Dir
        
    Loop
    
    VBA.MsgBox "All corresponding files opened! or Nothing Found!", , "Find Certs"
        
    askifcertsfound
    
End Sub

So now I'm trying to write code that will search through the workbooks that were opened to find the 3rd criteria I'm looking for and I might have 1 or 15 things I'm looking for. I want to highlight them so I can identify which ones I need to print out because I won't need all of them printed. I will have to manipulate the file before printing so I've written a pause function into the macro which allows me to do this. I'm having trouble making the macro search all open workbooks though (since the names of the files change each time), but it does work for the current workbook. Here is the code I'm using to search for the 3rd criteria and highlight if it's found:

Sub lookupcoil()

'coil to look for

   coilname = InputBox("coil ID")
    
    If coilname > 0 Then
        On Error GoTo ErrorHandler
        Cells.Find(What:=coilname, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
        ActiveSheet.Unprotect
            With Selection.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
            .PatternTintAndShade = 0
            End With
    
        Else
ErrorHandler:
        
        VBA.MsgBox "Coil not found", , "Find Coils"
              
    End If
   
    askforothercoils
    
End Sub

Can I modify the find to look at all open workbooks I tried using a loop workbooks and then putting this code in, but it gave me errors and kept locking up the code so it might just be a syntax error. I'm still new to this and trying to learn as I go. Any help would be appreciated. Thank you in advance.

TrowaD · Answer

Hi Tbonekiller,

The code below will loop through all opened workbooks and put 999 in cell B1 when A1 contains "aaa" as an example.

Sub RunMe()
Dim wb As Workbook
For Each wb In Application.Workbooks
    With wb.ActiveSheet
        If .Range("A1").Value = "aaa" Then .Range("B1").Value = 999
    End With
Next wb
End Sub

Best regards,
Trowa

Tbonekiller · Answer

Trowa,

That doesn't allow me to look in each file opened without retyping what I'm looking for each time it goes to the next workbook and then it still errors out on me. 

I was wanting something that I can enter the value I'm searching for and it will look through all open workbooks in cells c21:c26 for that value and highlight it. Then ask for the next thing to look for and search and highlight it, etc..etc.. 

I have the code that does it for the current workbook and then close that workbook when I'm done with it and then starts the process over with the next workbook, which is very time consuming. 

If I could get it to look through all workbooks with just the one entry from the user and then ask for the next thing to look for and then once everything is found I can then quickly flip through the opened workbooks and if there's anything highlighted in that particular workbook then I can manipulate and print it out.

I hope that makes since.

TrowaD · Answer

Hi Tbonekiller,

I was just showing you how to loop through opened workbooks.

Highlighting a specific value in range C21:C26 for all opened workbooks and coloring it yellow once found, can be done with the code below:
Sub RunMe()
Dim wb As Workbook
Dim sValue As String
Dim x As Integer

sValue = InputBox("Input search value")

For Each wb In Application.Workbooks
    With wb.ActiveSheet
        x = 20
        Do
            x = x + 1
            If .Cells(x, "C").Value = sValue Then
                .Cells(x, "C").Interior.ColorIndex = 6
            End If
        Loop Until x = 26
    End With
Next wb
End Sub

When you want to find another value, just re-run the code. Or maybe name this code ColorMeYellow and create another called ColorMeRed (and change colorindex from 6 to 3), to know what you searched for the second time.

Does that work for you?

Best regards,
Trowa

Tbonekiller · Answer

Yea stupid me. I told you I'm learning and it was a syntax error because all of the sheets are protected (with no password) just to keep people from changing the structure - hence the error I kept getting when it tried to change the background color. I modified your code to allow me to do that. So I'm using these (and several others) to do what I need done.

'So this finds all matching files, opens them, and unprotects them
Sub findcertsandlocatecoils()

' Opens all workbooks with partial match from input box so far
' This is step one
'
'

    Dim MyFolder As String
    Dim MyFile As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim pwd As String

    pwd = ""

    'size of wire
    filename1 = InputBox("enter size", _
    Title:="Size needed", Default:="IW")
    
    'heat number
    filename2 = InputBox("enter heat number", _
    Title:="Heat number needed", Default:="999")
    
    MyFolder = "Y:\FLO\ITSW\Quality Assurance (8200)\02 - Documentation\01 - Certificates\"
    MyFile = Dir(MyFolder & "\*" & filename1 & "*" & filename2 & "*.xlsx")
    Do Until MyFile = ""
        Workbooks.Open Filename:=MyFolder & "" & MyFile
        MyFile = Dir
        
    Loop

    For Each wb In Application.Workbooks
        For Each ws In wb.Worksheets
        ws.Unprotect Password:=pwd
    Next ws
Next wb
    
    VBA.MsgBox "All corresponding files opened! or Nothing Found!", , "Find Certs"
        
    askifcertsfound
    
    
End Sub

'and then this looks for the information I need to find in each workbook
Sub lookupcoils()
Dim wb As Workbook
Dim sValue As String
Dim x As Integer

sValue = InputBox("Coil ID")


For Each wb In Application.Workbooks
    With wb.ActiveSheet
      
        x = 20
        Do
            x = x + 1
            If .Cells(x, "C").Value = sValue Then
            .Cells(x, "C").Interior.ColorIndex = 6
            End If

        Loop Until x = 26
    End With
Next wb
        
        askforothercoils
End Sub

' and then this loops it back if there are other ones I need to find

Sub askforothercoils()
Dim Answer As VbMsgBoxResult

    Answer = MsgBox("Are there any other coils to look for?", vbYesNo + vbQuestion + vbDefaultButton1, "Coils to look for")
    
    If Answer = vbYes Then
    
    'If you click on yes then macro will look for other coils
    
        lookupcoils
        
        Else
    
        askifcoilsfound
    
        Exit Sub
     End If
     
End Sub


Thank you so much!!!! You are by far the most helpful and knowledgeable person I have found so far. Again thank you!

Search across all open workbooks for user defined criteria

4 responses

Similar discussions

Subscribe To Our Newsletter!