Loop taking much longer - looking for alternate options

Hi

I am trying to compare rows on two worksheets based on values in column A and column C respectively.

If the values match that means the value exists in sheet 1. In that case I want to update specific columns in sheet 1 with the values in specific columns in sheet 2.

If there is a value in sheet 2 which is not in sheet 1. It means it is a new values. So I add that to the last row.

I have a code that works well. Only problem is it takes much longer with 1000s of rows. Is there a way I could improve that or use other options.

Below is the code.

Please Login or Register  to view this content.

Just to clarify - I am on excel 2003.

Sheet 2 has latest data. I need to compare that with sheet 1 on ID column (column A and C respectively on sheet2 and sheet 1). If match then copy specific columns latest values to sheet 1. Else add a new row at the bottom with the specific column values.

The code works but too slow.

Backup your data.
I think the following will tighten up your logic. But I doubt that it will improve the speed of your code by much.
Can you upload your workbook (or a mockup of your workbook)?

Please Login or Register  to view this content.

Originally Posted by StevenM

Backup your data.
I think the following will tighten up your logic. But I doubt that it will improve the speed of your code by much.
Can you upload your workbook (or a mockup of your workbook)?

Please Login or Register  to view this content.

Thanks for your reply. I have created a mock up with your code above. It seems to be working fine logically. It is much tidy and compact. So I'll mostly use it in the real version.

The real version is quite complicated and has a lot more functionality, calculations and tabs. Hence, need to improve performance and size as much as possible.

Thanks again for your help.

Backup your data.
I haven't tested this code.
The idea behind my changes is very simple.
You have two blocks of code which copy cells from sheet2 to sheet1.
Each block contains around 20 lines.
Thus you are reading values from and to the workbook about 40 times for each block.
I've reduced this to just 3 per block.
Since all the cells are in the same row,
I copied two rows of data, one row from sheet 1, one row from sheet 2.
The values are transferred from one array to the other (just as you had it before).
Then the values in Sheet 1 are read back into the workbook.
To be honest, I've never tested this idea in a big project like yours.
I'll be interested to see if it really saves times (like I've been told).
Please let me know how it works out.

P.S. I hope I haven't made any errors when I was cutting and pasting.
P.S.S. Always use longs over integers (computers today find longs faster than integers).

Please Login or Register  to view this content.

Originally Posted by StevenM

Backup your data.
I haven't tested this code.
The idea behind my changes is very simple.
You have two blocks of code which copy cells from sheet2 to sheet1.
Each block contains around 20 lines.
Thus you are reading values from and to the workbook about 40 times for each block.
I've reduced this to just 3 per block.
Since all the cells are in the same row,
I copied two rows of data, one row from sheet 1, one row from sheet 2.
The values are transferred from one array to the other (just as you had it before).
Then the values in Sheet 1 are read back into the workbook.
To be honest, I've never tested this idea in a big project like yours.
I'll be interested to see if it really saves times (like I've been told).
Please let me know how it works out.

P.S. I hope I haven't made any errors when I was cutting and pasting.
P.S.S. Always use longs over integers (computers today find longs faster than integers).

Please Login or Register  to view this content.

Thanks here are the test results. I actually put a code for timer in the macro.

2.25 min with array and 2.38 min otherwise. So not much difference.

So not much difference.

I agree, it hardly seems worth the effort for such a small gain.

Originally Posted by StevenM

I agree, it hardly seems worth the effort for such a small gain.

Yes. Thanks for your help.