how to optimize finding matching values macro speed

Dear all,

I am writing to seek help in optimizing the following macro below, as is freezes and displays the out put after 10/15 minutes.

I have posted a same thread on the following forum below but did not get any response:

http://www.mrexcel.com/forum/excel-q...tch-value.html

Please Login or Register  to view this content.

Thank you for your time and help.

As far as I can tell, you have a bunch of data in Sheet2 range G1 to H?.
You want to look up preferably the value in H if it's there against sheet1 column C or if column G is populated then look up against Sheet1 column B?
The lookup value gives a corresponding row number for Sheet1 column A to be logged back in sheet2 column I.

Is my above understanding of what you're trying to achieve correct?

Firstly, on sheet1, can column A be moved to column D? If so, then you could do all of this with a worksheet formula I think.

If you need to do it with VBA code, then there a couple of ways to speed up your code execution, like loading Sheet1 contents into a second array rather than continuously reading from the worksheet.
Is it possible to upload a copy of the workbook?

dear all,

I am writing to seek help on creating a macro with the following logic below:

for example:
Sheet 1 input data - column A(Name), B(ID) and C (Lng_iD)

Sheet1.jpg

Sheet2 input data - Column G(ID) and H(lng_id):

Sheet2.jpg

Desired output after the macro has run in Sheet 2 - column I

Sheet2_desiredOuput.jpg



Please note: There are blank rows in between two datasets and the actual dataset can be viewed from post 3. The actual data has 130,000 lines of data, and if possible, could the macro processing time be under two minute or less.

Any help would be appreciated.
Thank you