Improving the speed of a loop through a date range

Maybe:

Sub Cycle() 

Dim i As Integer
Dim j As Integer
Dim k As Integer

i = 0
j = 3
k = 3

Range("B:C").NumberFormat = "m/d/yy h:mm;@"

'F1 is the date that calculations are based on
'P7 is the given start date/time
Range("F1").value = Range("P7").value

'Q7 is the end date/time
Do Until Range("F1").value >= Range("Q7").value
Range("F1").value = Range("P7").value + (i / 1440) 'this is to convert the day to a minute step-size

'O2 and O15 are the two calculated cells with either a 0 or 2 assigned
If Range("O2").value = 0 And Range("O15").value = 2 Then

'Pasting the date into the column
   
    Cells(j, 2) = Range("F1")
    
    j = j + 1

ElseIf Range("O2").value = 2 And Range("O15").value = 0 Then
    
    Cells(k, 3) = Range("F1")
        
    k = k + 1
    
Else
End If

i = i + 1

Loop

End Sub

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!

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

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