CODEWITHSUNDEEP
vbaconditional-statementsloops
Richard H
Richard H

Reputation: 39135

VBA - how to conditionally skip a for loop iteration

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

Upvotes: 123

Views: 444307

Answers (7)

Rahul Mehta
Rahul Mehta

Reputation: 41

VB6 , VBA doesnt have continue. Following HACK is what I have been using since VB3 days ie year 1992. I have been using "for dummy = 1 to 1 : exit for : next dummy" loop. The "exit for" inside dummy inner loop implements continue for actual outer loop

Sub Print_all_integers_except_multiples_of_3()
  Dim i As Integer, dummy As Integer
  For i = 1 To 100
  For dummy = 1 To 1 ' dummy only to implement Continue For i
    If i Mod 3 = 0 Then Exit For ' implements Continue For i
    Debug.Print i
  Next dummy
  Next i
End Sub

So inner dummy loop has only one iteration. And Exit For statement inside the inner dummy loop will implement "Continue For". Also, I dont indent For Dummy loop. So it becomes easily visible that this dummy loop is "dummy". And sometimes, I use variable name "Continue1" instead of Dummy to make it more obvious.

Upvotes: 0

AHeyne
AHeyne

Reputation: 3475

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

Upvotes: 45

richo7
richo7

Reputation: 1

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

Upvotes: -2

Singaravelan
Singaravelan

Reputation: 849

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

Upvotes: 5

mwolfe02
mwolfe02

Reputation: 24237

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.

Upvotes: 222

Jon Egerton
Jon Egerton

Reputation: 41579

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.

Upvotes: 15

Brian
Brian

Reputation: 7146

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

Upvotes: 34

Related Questions