0

I keep getting errors (either else without if or loop without do) in this, and I really have no idea 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
2
  • 3
    You are missing Next for your For loops Commented Aug 19, 2016 at 15:26
  • 3
    This is why proper indentation matters. Commented Aug 19, 2016 at 15:37

3 Answers 3

4

Try replacing the code below with what you have. You were missing 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've cleaned this up as well to make it easier to follow.

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
Sign up to request clarification or add additional context in comments.

8 Comments

+1 for taking your time to cleanly indent the code. Furthermore to improve code control and debugging I'd always have every Next followed by its iterator variable as well (Next j, Next k, Next i). You may want to edit your code and take this inside, too
Added the Next suffix as you suggested
@user3598756 IMO adding the iterator variable to the Next statement is merely mild clutter - if you need them for readability, then your code is likely too nested (which, with 6 levels deep, is definitely the case here) and you should be refactoring (extract method) instead of adding more visual noise.
@Mat's Mug agreed for seasoned developers, but for someone just learning the basics (which it appears is maybe true with the OP) I think it's helpful to understand how they are related. Without the iterator, it makes the relationship harder to visualize. When I code, I don't add the iterator.
Thanks guys! Yes very much leaning the basics, thanks for the tips
|
1

You are missing Next statement for all you Forloops

Try below 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

Comments

1

This is why proper 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

Now, you have 6 levels of nesting here, and quite much duplication. That's a sign you need to refactor and extract a procedure out of the inner nested code, to eliminate redundancies and improve the code's readability and maintainability (one modification should mean one single place to change the code).

If your code works as intended, I'd suggest you bring it (the whole procedure, or even the entire module!) over to Code Review for a cleanup and tips to improve and solidity your code (e.g. you probably don't need to Calculate as often as you do, and unqualified Range calls implicitly refer to the active worksheet - and this might cause unexpected bugs later!).

3 Comments

Thanks for the tips, I'll head over there now! Very much leaning the basics, so its very untidy I know, sorry!
@AlexW if you stick around and regularly put your working code up for review on Code Review, you'll quickly learn how to write clean, maintainable code. See you on the other side!
I do hope so! Ill keep practicing

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.