couple for each performance questions
 

i use a for each cell in range("L3:L33") statement. the previous code loads
about 40 workbooks, then cycles through 12 sheets using the for each statement.

it searches for an R or an A, can be either throughout the L3:L33 range. most
cells are blank, though. if it finds an R or A, it has to check offset(0,1) for
another value. i need to keep track of the counts of each of the 3.

i have the code working fine, so i don't really need code, just giving some
background on what i am doing. probably takes less than 2 seconds a workbook.

but i was wondering:

would there be a faster way instead of for each cell?

would it be any faster testing for a blank cell and skipping it and going to the
next cell.

--


Gary

couple for each performance questions
 

Hi Gary,
Why you don;t use find methode ?

Rgds,
Halim

Gary Keramidas menuliskan:
i use a for each cell in range("L3:L33") statement. the previous code loads
about 40 workbooks, then cycles through 12 sheets using the for each statement.

it searches for an R or an A, can be either throughout the L3:L33 range. most
cells are blank, though. if it finds an R or A, it has to check offset(0,1) for
another value. i need to keep track of the counts of each of the 3.

i have the code working fine, so i don't really need code, just giving some
background on what i am doing. probably takes less than 2 seconds a workbook.

but i was wondering:

would there be a faster way instead of for each cell?

would it be any faster testing for a blank cell and skipping it and going to the
next cell.

--


Gary

couple for each performance questions
 

the bottleneck will be in opening the 40 workbooks.

make 1 book with external references to the originals.
update the values.
process the results thru vba.

Or use ExecuteExcel4Macro to retrieve the arrays incl 2nd column
from a closed workbook.

vArray = executeexcel4macro("'d:\path\[file.xls]sheet1'!R2C12:R33C13")

google a bit to find nice wrappers for the macro strings.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Gary Keramidas wrote in

i use a for each cell in range("L3:L33") statement. the previous code
loads about 40 workbooks, then cycles through 12 sheets using the for
each statement.

it searches for an R or an A, can be either throughout the L3:L33
range. most cells are blank, though. if it finds an R or A, it has
to check offset(0,1) for another value. i need to keep track of the
counts of each of the 3.

i have the code working fine, so i don't really need code, just
giving some background on what i am doing. probably takes less than 2
seconds a workbook.

but i was wondering:

would there be a faster way instead of for each cell?

would it be any faster testing for a blank cell and skipping it and
going to the next cell.

couple for each performance questions
 

thanks, but a bit complicated for me. i'll stick to what i have, only takes 35
seconds.


--


Gary


"keepITcool" wrote in message
.com...

the bottleneck will be in opening the 40 workbooks.

make 1 book with external references to the originals.
update the values.
process the results thru vba.

Or use ExecuteExcel4Macro to retrieve the arrays incl 2nd column
from a closed workbook.

vArray = executeexcel4macro("'d:\path\[file.xls]sheet1'!R2C12:R33C13")

google a bit to find nice wrappers for the macro strings.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Gary Keramidas wrote in

i use a for each cell in range("L3:L33") statement. the previous code
loads about 40 workbooks, then cycles through 12 sheets using the for
each statement.

it searches for an R or an A, can be either throughout the L3:L33
range. most cells are blank, though. if it finds an R or A, it has
to check offset(0,1) for another value. i need to keep track of the
counts of each of the 3.

i have the code working fine, so i don't really need code, just
giving some background on what i am doing. probably takes less than 2
seconds a workbook.

but i was wondering:

would there be a faster way instead of for each cell?

would it be any faster testing for a blank cell and skipping it and
going to the next cell.

couple for each performance questions
 

i tried the varray method you posted and i get a 2015 error when using the
range. if i just use R2C12, i get the value of the cell.

when i loop through a sheet using a variable (mnth & "'!R" & icell & "C12"), to
do 33 rows on 12 sheets it takes 4 or 5 seconds.
if i use the workbooks open and loop through, it takes less than a second,
because 40 workbooks takes about 35 seconds.

so, unless storing an array of values and then evaluating the array values is
faster, i guess i'll keep the workbooks open method.

thanks, maybe i can use this in the future, though.
--


Gary


"keepITcool" wrote in message
.com...

the bottleneck will be in opening the 40 workbooks.

make 1 book with external references to the originals.
update the values.
process the results thru vba.

Or use ExecuteExcel4Macro to retrieve the arrays incl 2nd column
from a closed workbook.

vArray = executeexcel4macro("'d:\path\[file.xls]sheet1'!R2C12:R33C13")

google a bit to find nice wrappers for the macro strings.

--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Gary Keramidas wrote in

i use a for each cell in range("L3:L33") statement. the previous code
loads about 40 workbooks, then cycles through 12 sheets using the for
each statement.

it searches for an R or an A, can be either throughout the L3:L33
range. most cells are blank, though. if it finds an R or A, it has
to check offset(0,1) for another value. i need to keep track of the
counts of each of the 3.

i have the code working fine, so i don't really need code, just
giving some background on what i am doing. probably takes less than 2
seconds a workbook.

but i was wondering:

would there be a faster way instead of for each cell?

would it be any faster testing for a blank cell and skipping it and
going to the next cell.