How to make a macro end with a date criteria

[Ronnet2]

Hi,

I've made the following macro with a loop.

----

Sub Run_model_loop()

For i = 2 To 150

Value = i
ThisWorkbook.Sheets("EVENTS").Calculate
Sheets("SUB2").Rows(i - 1).Calculate
Sheets("SUB2").Rows(i).Calculate
Sheets("SUB3").Rows(i).Calculate
Sheets("SUB4").Rows(i).Calculate

Next i

ThisWorkbook.Sheets("EVENTS").Calculate
ThisWorkbook.Sheets("Order results").Calculate

End Sub

----

Right now the end trigger is when i = 150. However, the end trigger should be a specific end-date (cel B2 of sheet SYSTEM). I would like for the macro to check the date in column C (i.e. request date) of sheet SUB2. When it recalculates a row of SUB2, and the date in column C on that particular row >= the end date (i.e. SYSTEM!B2) then the macro should stop the loop.

Is this possible?

File: Event-Based Sim_(with loop macro).xlsm

[Ronnet2]

I've given it another go. Now I've put in a conditional If/then line. It seems to accept the ''Cells(i,3)'' reference, moving the ''end sub'' line before the ''next i'' line. However, I'm getting a ''block if without end if'' error. How can I fix this?


Sub Run_model_loop()

ThisWorkbook.Sheets("EVENTS").Calculate

For i = 2 To 150

Value = i
ThisWorkbook.Sheets("EVENTS").Calculate
Sheets("SUB2").Rows(i - 1).Calculate
Sheets("SUB2").Rows(i).Calculate
Sheets("SUB3").Rows(i).Calculate
Sheets("SUB4").Rows(i).Calculate

If Sheets("SUB2").Cells(i, 3) >= Sheets("SYSTEM").Cells(2, 2) Then
ThisWorkbook.Sheets("EVENTS").Calculate
ThisWorkbook.Sheets("Order results").Calculate
End Sub

Else
Next i

ThisWorkbook.Sheets("EVENTS").Calculate
ThisWorkbook.Sheets("Order results").Calculate

End Sub

[Alf]

You have an example of a "For i =" loop stopped by a given date in the "AddDate" macro.

Alf

[Ronnet2]

You have an example of a "For i =" loop stopped by a given date in the "AddDate" macro.

Alf

The EVENT sheet generates random dates (i.e. request times) on which events occur.
After each request time is generated, the event list is ranked based on this request time.
On sheet SUB2 these events are brought in on the correct ranked order according to the request date.
So the first time a request time (sheet SUB2 cell(3,i) >= to the end date (Sheet SYSTEM cell B2) then the macro should end.

I can't give an example because the request times are randomly generated.