Search across all open workbooks for user defined criteria

Solved
Tbonekiller
Posts
15
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
February 28, 2022
- Feb 23, 2022 at 10:44 AM
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
- Mar 1, 2022 at 11:56 AM
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.

4 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Feb 28, 2022 at 12:09 PM
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
1
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Feb 24, 2022 at 12:07 PM
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
0
Tbonekiller
Posts
15
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
February 28, 2022

Feb 24, 2022 at 12:54 PM
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.
0
Tbonekiller
Posts
15
Registration date
Wednesday August 21, 2019
Status
Member
Last seen
February 28, 2022

Feb 28, 2022 at 02:25 PM
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!
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
510
Mar 1, 2022 at 11:56 AM
Good to hear you got it working. Glad I could help!
0