Max string size / best way to loop through 200k rows

Hi all,

I know I'm cheating a bit by stating two questions in the topic, but they're related.

I have two workbooks - one with 9,000 rows, another with 200,000 rows.
On the 9,000 rows, I need to find if they're available in the one with the 200,000 rows and if so, copy a value from there to the 9,000row sheet.

For quick comparison, I tried to do the following:

Please Login or Register  to view this content.

however, then the following then gives me 63,749 (the number of rows in Sheet1 in Workbook ("A") = 194,544)

Please Login or Register  to view this content.

And I can't for the love of god not figure out why.

The essence of my code:

Please Login or Register  to view this content.

However, this takes forever, even though it's only 9K rows....
Any suggestions?

Transpose function works correctly only 2^16, 65536 rows.
Not reading much of your code, I would use array and dictionary co compare/output.