couple for each performance questions

[Gary Keramidas]

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

[[email protected]]

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

[keepITcool]

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 <news:<#[email protected]>

> 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 Keramidas]

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


--


Gary


"keepITcool" <[email protected]> wrote in message
news:[email protected]...
>
> 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 <news:<#[email protected]>
>
>> 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 Keramidas]

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" <[email protected]> wrote in message
news:[email protected]...
>
> 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 <news:<#[email protected]>
>
>> 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.