Countif in multiple books
My oversight. Change
set wkbk = workbooks.open(sName)
to
set wkbk = workbooks.open("C:\MyFolder\" & sName)
--
Regards,
Tom Ogilvy
"Gareth" wrote in message
...
Tom
I'm getting run-time error 1004, "myfile.xls could not be
found". but it is there!
Any suggestions?
Gareth
-----Original Message-----
Something like:
sub CountKKL()
Dim sName as String
Dim wkbk as Workbook
sname = Dir("C:\Myfolder\*.xls")
do while sName < ""
set wkbk = workbooks.open(sName)
cnt = cnt + Application.Countif(rng,"KKL")
wkbk.Close Savechanges:=False
sName = dir()
Loop
workbooks.Add
ActiveWorkbook.Worksheets(1).Range("A2").Value = cnt
End Sub
Make sure the workbook containing the code is not in your
directory and all
of the 518 files are not open in excel.
--
Regards,
Tom Ogilvy
"Gareth" wrote in
message
...
I have a folder with 518 files, I need to count the
number
of times "KKL" occurs in a range called "rng1" and then
return the value to cell A2 in a new book.
Is this possible?
I don't fancy having to do it all manually.
Thanks in advance.
Gareth
.
|