Improving the speed of a loop through a date range

Maybe:

Please Login or Register  to view this content.

Hi,

Rather than use loops which are inherently slow and if there is standard functionality available you should use that.
In this case I'd be inclined to use a standard IF function to populate the columns 1 or 2 with your dates.

You could enlist the help of a macro to do this and add the function to the relevant block of cells in a single instruction. The big advantage is that you don't need a loop of any sort, either a DO loop or a FOR loop

Thanks for all the input!

xladept: That definitely makes the copying and pasting thing I had much simpler.
Unfortunately the overall speed is the same. I wonder if it has more to do with the calculations its doing each time rather than looping through the date.

StuCram: I did try that but it still takes the same amount of time

Richard: I think the only problem with that is I don't know which dates needed to be copied and pasted over until I cycle through it. A piece of equipment can be assigned a number (0, 1, or 2) referring to (DOWN, SLOW, UP) at any given time based on calculated values. I'm interested in the transition from 0 to 2 (i.e. DOWN to UP or UP to DOWN). However, I don't know when that transition occurs unless I look at every specific time. But you bring up a good point about how I approach it; maybe I need to completely rethink how to gather the dates without having a loop.

I'll keep working on it!

How are the O's calculated??

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic