Analyze Within An Array and Pass Back Once vs. Pass at each applicable Instance

A previous poster posed a problem outlined below (and demanded a programmatic approach, despite multiple attempts to 'nudge' a formulaic approach).

I need a VBA based macro to find all matching jobs which are in Sheet1 column A, in Sheet2 column A and return the corresponding value in column B in Sheet2 to Column B, Sheet 1.

I originally solved his the query (under the disclaimer that an array approach would be more efficient) with a 2x For Each Next; Offset syntax.

I have since reapproached the issue for my own personal development via array. As it is widely held that the most efficient code touches the worksheet the least amount of times, my question is this.

How would my code below (which works) be modified to store all instances of TRUE within the

Please Login or Register  to view this content.

line within the array and pass those back to the worksheet in one fell swoop.

Full code:

Please Login or Register  to view this content.

Friday bump

Friday-Bump

you could just use another array, dimensioned to match the row count and populate that with the values in the loop, then write back to the sheet at the end.

Any chance you could show me how that might possibly look?

AlvaroSiza,

This is how I would do it. It only uses a single array, and uses the .Find method to look for matches and then loads it into the array. It outputs the array at the end:

Please Login or Register  to view this content.