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

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

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


.

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


.

Tom

Sorry to be a pain but the code is stopping at this line:

cnt = cnt + Application.Countif(rng,"KKL")

Run-time error 13 - Type mismatch.

Any ideas?

-----Original Message-----
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


.



.

Assuming you have a name defined as rng1 in each file, then it would be

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(Range("rng1"),"KKL")
wkbk.Close Savechanges:=False
sName = dir()
Loop
workbooks.Add
ActiveWorkbook.Worksheets(1).Range("A2").Value = cnt
End Sub

I actually had intended to do

sub CountKKL()
Dim sName as String
Dim wkbk as Workbook
Dim rng as Range
sname = Dir("C:\Myfolder\*.xls")
do while sName < ""
set wkbk = workbooks.open(sName)
On Error Resume Next
set rng = wkbk.names("rng1").RefersToRange
On Error goto 0
if not rng is nothing then
cnt = cnt + Application.Countif( _
rng,"KKL")
End if
wkbk.Close Savechanges:=False
sName = dir()
Loop
workbooks.Add
ActiveWorkbook.Worksheets(1).Range("A2").Value = cnt
End Sub

but must have gotten distracted.

--
Regards,
Tom Ogilvy