looking for a way to increase macro performance

Currently this portion of my code takes about 15-20 minutes to run. I was wondering if any one had any suggestions on how to increase the speed of this code. Screen updating and display updates would already be set to false and calculations are set to manual. just would like to know if there is a way to replace the IF Else commands with something that will accomplish the same results quicker.

thanks, s4

Please Login or Register  to view this content.

that sounds like a good place to start. however i am struggling combining the two loops. the first loop fills in missing data on the original/source tab. the second loop moves the data based on certian criteria from the source tab to a new tab. when I try to combine them it skips the second loop entirely. do you know of a way to to this without using IF Else?

yes, I was wondering if there was another command that could replace the If Then Else. I don't have a sample file available due to confidential content. where I'm struggling on combing the loops is I need to look at each row and verify that all the data is present in the original table. if it's not then I would have to perform the "original first" loop, then while on the same row check the criteria to determine what part of the "original second" loop to execute. so in some cases this code needs to execute

Please Login or Register  to view this content.

then execute one of these 3 block Ifs

Please Login or Register  to view this content.

I'm not sure how to code it so it know when to do both (if needed) or to skip the "first" step and simply execute the proper block if in the "second" step.

But I agree if there is a way to combine the two loops it should save significant time.

This is not tested, but perhaps it would give you ideas if it doesn't work(likely).

Please Login or Register  to view this content.

AlphaFrog -
thanks for the different approach, However even when I modified to get it to work properly it still takes the about the same amount of time to execute. I do like the fact that the code itself is more condensed.

Norie/AlphaFrog-
I appreciate your interests in my post but I have been unable to make the improvements I was hoping to. I am going to keep searching around for another solution. When and if I find a quicker way I will post the changes I made.

yes, thanks. I haven't been able to solve how to combine the first loop effectively with each of the 3 scenarios of the 2nd. Still playing with it, I feel it will save the most time since I would only be looping thru each row once instead of twice.

What I originally posted is one of 5 sections that I created individually then combined into one macro. That section of the code takes close to 20 minutes to execute with the data I have. thanks for the vlookup tip I will give that a go in a moment.

probably, but I was just looking for some quick suggestions. Combining the vlookup didn't save me much time with this file due to the fact there was less than a handful of cases where it was needed. But I have some other applications where it will come in more handy. thanks again.

I figured out a way to decrease the time it takes to execute dramatically. In it's orignal state it took around 25-30 minutes to run, now it takes less than 3 minutes. I was able to successfully merge some of the steps in my original code and elminate the need of using some lookup functions on a chunk of the data. The part that I merged followed directly below the code I originally posted and look like this,

Please Login or Register  to view this content.

After merging it with the section from my original post it now looks like this,

Please Login or Register  to view this content.

Doing that I was now only looping through each row once and using fewer time consuming functions. Thanks for pointing that out Norie.