Need help writing a better loop

[alexduy]

I need to find a better way to write the code below. The code below works great, but then it's looping through my whole data in order to accomplish what I want. The thing is my data can have 40,000 rows and it's taking forever. Is there a better way to write this?

Basically, what the code below doing is looping through my data and evaluate the data in column D and G. If they are both "CONTRACT" and "ABC COMPANY". If that's the case, it will change the value in column D, which is "CONTRACT" to "CONTRACT ENG".


I think another way to write the code is to do an autofilter and filer column D and G by "CONTRACT" and "ABC COMPANY". Once it's filtered, it would only be less than 20 rows of data that match that criteria. I can loop through those 20 rows, it's much faster than have the macros loop through 40k rows. I just don't know how to do it. Can someone help me?

Please Login or Register  to view this content.

[alansidman]

If you provide a sample worksheet then I am sure we can provide you with a solution using Advanced Filter with or without VBA. Read the yellow banner at the top of this page.

[maniacb]

Try something like this

Please Login or Register  to view this content.

[alansidman]

Administrative Note:

@maniacb

You need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional.)

[alexduy]

Hi Alansidman,

I've attached a workbook. Is it possible to write a script so that before the loop is run, the macro would check column D to see if the string "CONTRACT" exist. If it's not there, skip and move on with the macro.

If it exists, then loop through column D and G to find CONTRACT in column D and ABC in column G. If both of those criteria match, then change CONTRACT to CONTRACT ENG.

My script works well, it's just that sometime the string CONTRACT doesn't exist, the script still run and loop through 40 rows for nothing. Also, if the string exist, it still loop through 40k rows to make the change and usually I only have to change 3 or 4 rows.

[alexduy]

maniacd - I tried your code and it did not work.

[maniacb]

Try it this way

Please Login or Register  to view this content.

[AlphaFrog]

Please Login or Register  to view this content.

[alexduy]

Thank you both very much. I was able to use both your codes and create my own code to get what I want. I really appreciate your help.

Here is the code in case someone want to learn:

Please Login or Register  to view this content.