Hundreds of workbooks values that match 493

[scotsrule08]

Good day fellow Mr Excel Experts!

I have a folder with hundreds of spreadsheets. I need to identify any spreadsheet with the value $493 in column B. There is not a specific cell that 493 would be in, and not every spreadsheet has 493 in column B.

I jut need a list with the file name of any spreadsheet with the value 493 somewhere in column B.

There will be many other values in column B besides 493.

any help is appreciated!

Thank you!

 

[Andrew Fergus]

Is it always exactly 493? Or could there be examples like 1,493 or 493.5 that you also want to return? Or do you want to ignore these variations and return only those workbooks that have exactly the value 493 somewhere in column B?

 

[scotsrule08]

It will always be $493.00

 

[scotsrule08]

I only need to return the file name for the value $493

 

[Eric W]

Open a new workbook. Put the path of the folder in A1 like this:

C:\Topfolder\Subfolder\

the last backslash is required. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module, and paste this code in the window that opens:

Public Sub GetNames()
Dim MyPath As String, MyName As String, MyRow As Long, MyTot As Long, MyCtr As Long

    MyPath = Range("A1").Value
    
    MyTot = 0
    MyRow = 0
    MyCtr = 0
    Range("A2").Formula = "=COUNTIF(B:B,493)"
    
    Application.ScreenUpdating = False
    
    MyName = Dir(MyPath & "*.xl*")
    Do While MyName <> ""
        MyTot = MyTot + 1
        MyName = Dir()
    Loop
    
    MyName = Dir(MyPath & "*.xl*")
    Do While MyName <> ""
        MyCtr = MyCtr + 1
        Application.StatusBar = "Processing file " & MyCtr & " of " & MyTot
        Range("B:B").Formula = "='" & MyPath & "[" & MyName & "]Sheet1'!B1"
        If Range("A2").Value > 0 Then
            MyRow = MyRow + 1
            Cells(MyRow, "F").Value = MyName
        End If
        MyName = Dir()
    Loop
    Range("B:B,A2").ClearContents
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
    MsgBox MyTot & " file(s) read. " & MyRow & " file(s) found with 493 in column B of Sheet1"
    
End Sub

Note the line in red. This creates a formula that references the closed workbook without opening it. It assumes that the column you want to search is on Sheet1. Change if needed. If you need to search multiple sheets, we'll probably need to actually open the file. Also note that if you know the data only goes down say 1000 lines, you can vastly increase the speed of the macro by changing the first part of that line to:

Range("B1:B1000").Formula =

Return to Excel. Press Alt-F8, choose GetNames and click Run. You can see a status message in the status bar at the bottom as it runs. The final list will be in column F.

Let us know how this works.

 

[scotsrule08]

Open a new workbook. Put the path of the folder in A1 like this:

C:\Topfolder\Subfolder\

the last backslash is required. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module, and paste this code in the window that opens:

Public Sub GetNames()
Dim MyPath As String, MyName As String, MyRow As Long, MyTot As Long, MyCtr As Long

    MyPath = Range("A1").Value
    
    MyTot = 0
    MyRow = 0
    MyCtr = 0
    Range("A2").Formula = "=COUNTIF(B:B,493)"
    
    Application.ScreenUpdating = False
    
    MyName = Dir(MyPath & "*.xl*")
    Do While MyName <> ""
        MyTot = MyTot + 1
        MyName = Dir()
    Loop
    
    MyName = Dir(MyPath & "*.xl*")
    Do While MyName <> ""
        MyCtr = MyCtr + 1
        Application.StatusBar = "Processing file " & MyCtr & " of " & MyTot
        Range("B:B").Formula = "='" & MyPath & "[" & MyName & "]Sheet1'!B1"
        If Range("A2").Value > 0 Then
            MyRow = MyRow + 1
            Cells(MyRow, "F").Value = MyName
        End If
        MyName = Dir()
    Loop
    Range("B:B,A2").ClearContents
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
    MsgBox MyTot & " file(s) read. " & MyRow & " file(s) found with 493 in column B of Sheet1"
    
End Sub

Note the line in red. This creates a formula that references the closed workbook without opening it. It assumes that the column you want to search is on Sheet1. Change if needed. If you need to search multiple sheets, we'll probably need to actually open the file. Also note that if you know the data only goes down say 1000 lines, you can vastly increase the speed of the macro by changing the first part of that line to:

Range("B1:B1000").Formula =

Return to Excel. Press Alt-F8, choose GetNames and click Run. You can see a status message in the status bar at the bottom as it runs. The final list will be in column F.

Let us know how this works.

Thank you for the quick response! I am getting the error "Run-time error '1004': Method 'Range' of object'_Global' failed" for the line:

Range("B:B").Formula = "='" & MyPath & "[" & MyName & "]Sheet1'!B1"

 

[Eric W]

I admit, getting that line to work can be finicky. It works in my test workbook, but I'm not sure how to troubleshoot it in yours at a distance. You could try this version of the macro instead:

Public Sub GetNames()
Dim MyPath As String, MyName As String, MyRow As Long, MyTot As Long, MyCtr As Long

    MyPath = Range("A1").Value
    
    MyTot = 0
    MyRow = 0
    MyCtr = 0
    
    Application.ScreenUpdating = False
    
    MyName = Dir(MyPath & "*.xl*")
    Do While MyName <> ""
        MyTot = MyTot + 1
        MyName = Dir()
    Loop
    
    MyName = Dir(MyPath & "*.xl*")
    Do While MyName <> ""
        MyCtr = MyCtr + 1
        Application.StatusBar = "Processing file " & MyCtr & " of " & MyTot
        Workbooks.Open Filename:=MyPath & MyName
        If WorksheetFunction.CountIf(Range("B:B"), 493) > 0 Then
            MyRow = MyRow + 1
            ThisWorkbook.ActiveSheet.Cells(MyRow, "F").Value = MyName
        End If
        ActiveWorkbook.Close savechanges:=False
        MyName = Dir()
    Loop
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
    MsgBox MyTot & " file(s) read. " & MyRow & " file(s) found with 493 in column B of Sheet1"
    
End Sub

This version actually opens the files. And as such, it checks column B of whatever sheet is on top when it's opened. If your data is on a specific sheet that may not be on top, we can add a

Sheets("MySheet").Select

line.