Code works but takes hours to run.

Hello mikemarsha,

Welcome to the Forum.

Have you tried to turn Screenupdating off and on, and the same with Calculation from manual to Automatic?

If you had posted your complete Code, I could have added it for you, which could have made a difference.

Regards

I don't know if this is the primary problem but you almost never need to (or want to) activate and select things to operate on them. It adds a lot of extra useless work and will slow down execution. Also I suspect you are not showing enough code for a full diagnosis. This is because you have an unmatched Loop statement at the end. What is going on in that outer loop? Can you attach your file? Or at a minimum post all the code?

It might help if you explained your logic but I'm guessing you don't need that If statement. The loop will exit the first time that the cell equals a at the end of the loop. Which means the If test is always going to be True, after the first pass through the loop. Is that what you intended? I'm reading this as you want to look for the first cell after the active cell that is equal to a or blank, and replace the value with b. That's what this version does:

Please Login or Register  to view this content.

If instead you want to look for the first cell including the initial active cell with a match, I would suggest you flip your conditions and use While:

Please Login or Register  to view this content.

These changes would speed things up noticeably but I don't think it will get you from hours to seconds.

Here is the complete code for the sub. 1st I load my variables a = my item number, b = the qty and c = the week start date. ( I have the variable set to nothing to begin with becuase I will loop through this entire process and when I start the loop again I want my varibles emptied out). I then move to the 1st tab and find the date that matches variable c in row 20. At that point my code looks for the cell in column a that has the item number that matches variable a. When it finds that item I load variable b into the empty cell were they intersect. My if statement is basically moving my activecell down the column my date was found in until it finds that correct item in column a. Thats a total of 9,194 cells it may have to check which is why it takes so long to run.

Please Login or Register  to view this content.