Syntax errors in VBA

Question

I keep getting errors (either without if or loop without do), and I really don't know why ... can anyone help ?! Thanks!

Do Until (Range("I4").Value = 0) For i = 2 To lLastrow If Range("G" & i).Value = 0 Then i = i + 1 ElseIf Range("G" & i).Value < 0 Then Do Until (Range("G" & i).Value = 0) For j = 0 To i If Range("F" & i - j).Value < 0 Then Range("F" & i - j).Value = Range("F" & i - j).Value + 1 Else: j = j + 1 End If Application.Calculate Loop ElseIf Range("G" & i).Value > 0 Then Do Until (Range("G" & i).Value = 0) For k = 0 To i If Range("F" & i - k).Value > 0 Then Range("F" & i - k).Value = Range("F" & i - k).Value - 1 Else: k = k + 1 End If Application.Calculate Loop End If Application.Calculate Loop 

Answer 1

Try replacing the code below with what you have. You missed Next for each of your For loops. Do and Loop go together, similarly For and Next go together (they form the basis for the loop).

Here's the code, I cleaned it up to make it easier to execute.

 Do Until (Range("I4").Value = 0) For i = 2 To lLastrow If Range("G" & i).Value = 0 Then i = i + 1 ElseIf Range("G" & i).Value < 0 Then Do Until (Range("G" & i).Value = 0) For j = 0 To i If Range("F" & i - j).Value < 0 Then Range("F" & i - j).Value = Range("F" & i - j).Value + 1 Else j = j + 1 End If Application.Calculate Next j Loop ElseIf Range("G" & i).Value > 0 Then Do Until (Range("G" & i).Value = 0) For k = 0 To i If Range("F" & i - k).Value > 0 Then Range("F" & i - k).Value = Range("F" & i - k).Value - 1 Else k = k + 1 End If Application.Calculate Next k Loop End If Next i Application.Calculate Loop 

Answer 2

You are missing the Next instruction for all of you. For loops

Try entering the code

 Do Until (Range("I4").Value = 0) For i = 2 To lLastrow If Range("G" & i).Value = 0 Then i = i + 1 ElseIf Range("G" & i).Value < 0 Then Do Until (Range("G" & i).Value = 0) For j = 0 To i If Range("F" & i - j).Value < 0 Then Range("F" & i - j).Value = Range("F" & i - j).Value + 1 Else j = j + 1 End If Application.Calculate Next'You missed this Loop ElseIf Range("G" & i).Value > 0 Then Do Until (Range("G" & i).Value = 0) For k = 0 To i If Range("F" & i - k).Value > 0 Then Range("F" & i - k).Value = Range("F" & i - k).Value - 1 Else k = k + 1 End If Application.Calculate Next 'You missed this Loop End If Application.Calculate Next 'You missed this Loop 

Answer 3

This is why correct and consistent indentation matters:

 Do Until (Range("I4").Value = 0) | For i = 2 To lLastrow | | If Range("G" & i).Value = 0 Then | | | i = i + 1 | | ElseIf Range("G" & i).Value < 0 Then | | | Do Until (Range("G" & i).Value = 0) | | | | For j = 0 To i | | | | | If Range("F" & i - j).Value < 0 Then | | | | | | Range("F" & i - j).Value = Range("F" & i - j).Value + 1 | | | | | Else | | | | | | j = j + 1 | | | | | End If | | | | | Application.Calculate | | | | Next '<<<<<<< MISSING!! | | | Loop | | ElseIf Range("G" & i).Value > 0 Then | | | Do Until (Range("G" & i).Value = 0) | | | | For k = 0 To i | | | | | If Range("F" & i - k).Value > 0 Then | | | | | | Range("F" & i - k).Value = Range("F" & i - k).Value - 1 | | | | | Else | | | | | | k = k + 1 | | | | | End If | | | | | Application.Calculate | | | | Next '<<<<<<< MISSING!! | | | Loop | | End If | | Application.Calculate | Next '<<<<<<< MISSING!! Loop 

You now have 6 breeding levels and quite a lot of duplication. This is a sign necessary for the reorganization and extraction of the procedure from the internal embedded code, to eliminate redundancy and improve the readability of the code and maintainability (one modification should mean one place for changing the code).

If your code works as intended, I suggest you bring it (the whole procedure or even the whole module!) To the Code Review for cleaning and tips for improving and maintaining the integrity of your code (for example, you probably don't need Calculate as often as you do, and unqualified Range calls implicitly reference the active sheet - and this can lead to unexpected errors later!).