VBA - how to conditionally skip a for loop iteration
VbaConditionalLoopsVba Problem Overview
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
Vba Solutions
Solution 1 - Vba
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.
Solution 2 - Vba
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
Solution 3 - Vba
Couldn't you just do something simple like this?
For i = LBound(Schedule, 1) To UBound(Schedule, 1)
If (Schedule(i, 1) < ReferenceDate) Then
PrevCouponIndex = i
Else
DF = Application.Run("SomeFunction"....)
PV = PV + (DF * Coupon / CouponFrequency)
End If
Next
Solution 4 - Vba
Continue For
isn't valid in VBA or VB6.
From this MSDN page it looks to have been introduced into VB.Net in VS 2005./Net 2.
As the others have said there's not really an option other than to use Goto
or an Else
.
Solution 5 - Vba
Hi I am also facing this issue and I solve this using below example code
For j = 1 To MyTemplte.Sheets.Count
If MyTemplte.Sheets(j).Visible = 0 Then
GoTo DoNothing
End If
'process for this for loop
DoNothing:
Next j
Solution 6 - Vba
Maybe try putting it all in the end if and use a else to skip the code this will make it so that you are able not use the GoTo.
If 6 - ((Int_height(Int_Column - 1) - 1) + Int_direction(e, 1)) = 7 Or (Int_Column - 1) + Int_direction(e, 0) = -1 Or (Int_Column - 1) + Int_direction(e, 0) = 7 Then
Else
If Grid((Int_Column - 1) + Int_direction(e, 0), 6 - ((Int_height(Int_Column - 1) - 1) + Int_direction(e, 1))) = "_" Then
Console.ReadLine()
End If
End If