VBA - how to conditionally skip a for loop iteration

Question

I have a for loop over an array. What I want to do is test for a certain condition in the loop and skip to the next iteration if true:

For i = LBound(Schedule, 1) To UBound(Schedule, 1)     If (Schedule(i, 1) < ReferenceDate) Then         PrevCouponIndex = i         Continue   '*** THIS LINE DOESN'T COMPILE, nor does "Next"     End If     DF = Application.Run("SomeFunction"....)     PV = PV + (DF * Coupon / CouponFrequency) Next 

I Know I can do:

 If (Schedule(i, 1) < ReferenceDate) Then Continue For 

but I want to be able to record the last value of i in the PrevCouponIndex variable.

Any ideas?

Thanks

Answer 1

VBA does not have a Continue or any other equivalent keyword to immediately jump to the next loop iteration. I would suggest a judicious use of Goto as a workaround, especially if this is just a contrived example and your real code is more complicated:

For i = LBound(Schedule, 1) To UBound(Schedule, 1)     If (Schedule(i, 1) < ReferenceDate) Then         PrevCouponIndex = i         Goto NextIteration     End If     DF = Application.Run("SomeFunction"....)     PV = PV + (DF * Coupon / CouponFrequency)     '....'     'a whole bunch of other code you are not showing us'     '....'     NextIteration: Next 

If that is really all of your code, though, @Brian is absolutely correct. Just put an Else clause in your If statement and be done with it.

Answer 2

You can use a kind of continue by using a nested Do ... Loop While False:

'This sample will output 1 and 3 only  Dim i As Integer  For i = 1 To 3: Do      If i = 2 Then Exit Do 'Exit Do is the Continue      Debug.Print i  Loop While False: Next i