VBA Arrays macro performance

[sepi]

Hi,

I have two sheets (Header & Item) in one file. Header has 25k and Item 50k rows. I'm looping through col B in Header and looking values in col B in Item and when we have a match then copying some values from Header-sheet to Item-sheet. I want this to be as fast as possible.

I have two different macros to do this, both doing what I want and working basically fine. One uses arrays and the other one not. I read about arrays and thought that they should make this faster but the results are quite contrary. Array macro takes 336 seconds and the other one 42 seconds to accomplish.

Is there something terribly wrong with my array macro or why is it so slow...? If there are any other comments or best practises concerning these macros, I would be glad to hear them. Thanks!

Please Login or Register  to view this content.

And the other macro as well:

Please Login or Register  to view this content.

[nilem]

Hi Sepi,
look at this thread. Maybe it's would suit you

[JosephP]

worksheetfunction tends to be faster with ranges than arrays-I reckon that's probably why your array version is slower

[AB33]

Jay,
I thought this line is

Looping through a range while this line is

Please Login or Register  to view this content.

Please Login or Register  to view this content.

is looping through the array.

I am not sure what this line is

Please Login or Register  to view this content.

,But it appears that the arrays are copied in to this line.

[JosephP]

how does that relate to what I said? I'm talking about

Please Login or Register  to view this content.

being faster than

Please Login or Register  to view this content.

[JosephP]

if you want to test with a blank worksheet

Please Login or Register  to view this content.

[AB33]

Jay,
Okay, I see your point!