How to Optimize This Code?

Good night people,

I have a task to do which I use a find and replace code that works by selection:

Please Login or Register  to view this content.

The code is great and does the job, but it can take hours to process when I'm working on a 100K rows with cells to be found and replaced by 100k different matches from another worksheet.

Could anyone suggest me something to improve the performance?


PS: I'm not exactly sure how it affects the coding, but the column contains blank cells between the values. Maybe telling it to auto skip it would save it some time? (sorry if it sounds stupid, I'm kind of new with excel)

See if this helps

Please Login or Register  to view this content.

Good morning AlphaFrog, thank you for the reply

I saw your message yesterday and left the code running through the night. I can't really tell if it made it somehow faster, as it was still running in the morning when I woke up, but I found out something interesting. When I press ESC and end the code, the data seems to have been replaced properly, although the code was running as far as I could check everything was actually replaced, maybe something makes the code keep running for some reason.

I will upload a small sample as suggested by our friend above, that way you can see how the data is structured. All columns are located in the correct position according to the original file, but as the code works by selection, I only pasted the columns that are affected.


PS: Sheet3 has the column that's gonna be replaced, based on the 2 columns in the other sheet. I also didn't mention that the column with the data to be replaced has a text "Código" as a header, which is used later on for another code, and also the dates, both data that can't be deleted, but maybe could be causing the problem

Hello Jolivanes, thanks for the reply!

I'm uploading here a small sample of the file. I gave some information above that might be relevant to us.

What are you actually wanting to achieve here...can you explain the process...
What would the first range selection be and what must replace what?

Must sheet3's product code be replace by sheet4 corresponding Code category...And why must the ranges be selected...Surely all must just be updated?
i.e.
202350 replaced by 64?

Originally Posted by sintek

What are you actually wanting to achieve here...can you explain the process...

Hello sintek, thanks for the interest!

My goal is to replace all the product codes (SKU) for the category code. I have a periodic data sheet that contains all transactions identifying the client ID. The original the file has all the SKU numbers also, which can be used to identify which category it belongs. I'm not sure if this is the best way of doing it, but for my actual knowledge it suits, as I don't have much more time to study before completing this task. The only reason for selection is that it makes it easier, as each data sheet can vary on lenght and also for using 2 different worksheets.

I tried adapting the code to skip dates and text and it seems to have worked faster, although it still demands a couple hours. I will keep using this for now, until and if someone has any other idea that could help.

Please Login or Register  to view this content.

My goal is to replace all the product codes (SKU) for the category code

So why not simply...

Please Login or Register  to view this content.

Maybe try

Please Login or Register  to view this content.

Does this work for you?
Change references where required.

Please Login or Register  to view this content.

With 100,000 rows of data in Sheet3 and no changes to Sheet4, it took 3 seconds on my old machine with excel 2007.

Originally Posted by sintek

So why not simply...
[/CODE]

This worked incredibly fast!!! But some cells that were blank, for some weird reason appear with a date value 00/01/1900. Any idea why?


EDIT: The blank fields were formatted as dates, that's the reason. Duh


Thank you very much sintek!! You just saved me some weeks! s2

As for the other codes, both made my Excel crash after freezing. Anyway, I think the code from the message above might be efficient enough, but I thank you for your help.

Originally Posted by sintek

So why not simply...

Sintek,

There are actually 2 problems with the code. The first is that it clears the following column to the right. It's not a big deal as I can paste it from the original sample. The other problem is whenever I run your macro to replace the codes, I can't run another macro that concatenates the values on column M and N. This other macro works fine with the SKU numbers, and was working fine with the first macro I posted that took hours. I've been checking for hours for any difference between the original sheet and the sheet after running your macro, but I can't find anything different.

When I run this code below, I get a Run Time Error 9 Subscript Out of Range.

Please Login or Register  to view this content.

Any idea what could be causing the problem?


EDIT: The code above concatenates all values into a single cell, instead of working and supposed: counting the blanks and the text "Código" to determine a group of data that belongs to a single customer ID. I'm not sure how this helps, but the info might be useful

Post 10 above is actually the way to go with this problem...as it just references the required column and replaces the correct lookup found values...

The first is that it clears the following column to the right

This was just a solution for your sample file uploaded...One would need to use a helper column at end of data...

I can't run another macro that concatenates the values on column M and N

As far as above...One can only see if you upload a sample file to test...As we cannot recreate the issue

Just for future reference...the below declarations...
Only the red ones are actually declared...the ones prior without As are declared as variants...

Please Login or Register  to view this content.

Should be...

Please Login or Register  to view this content.