Count worksheet with specific value in a cell

[John Chow]

I was trying to locate (or simply count) from a list of 1000 excel workbooks
(which may contain more than one worksheet) with a specific value in a cell.

Say, the value "ak" in cell <R59>.

Would anyone show me the way, please.

Thanks!


John Chow

 

[Don Pistulka]

John,

I took this code from Ron de Bruin's web site and changed a few lines. It
goes through each Excel file in folder "C:\data" (change to fit) and counts
how many values "AK" are found in cell R59 , sheet1 of each file. Go to
Ron's web site for other macros to search workbooks:
http://www.rondebruin.nl/tips.htm

Sub Countcells()
Dim basebook As Workbook
Dim mybook As Workbook
Dim i As Long
Application.ScreenUpdating = False
With Application.FileSearch
.NewSearch
.LookIn = "C:\data"
.SearchSubFolders = False
.Filename = "*.xls"
If .Execute() > 0 Then
Set basebook = ThisWorkbook
basebook.Sheets(1).Range("a1").Value = 0
For i = 1 To .FoundFiles.Count
Set mybook = Workbooks.Open(.FoundFiles(i))
If mybook.Sheets(1).Range("r59").Value = "AK" Then
basebook.Sheets(1).Range("a1").Value = basebook.Sheets(1).Range("a1").Value
+ 1
mybook.Close True
Next i
End If
End With
Application.ScreenUpdating = True
End Sub


Don Pistulka