Trying to add to VBA code module (keep getting compile error)

[craigwojo]

I'm a green newbie to VBA (like today).
I was given this VBA code for my spreadsheet from Joe4 and I'm needing to add an addon to the code. Please look at the code and see what is wrong and why I am getting a compile error on the "Next" word (colored red) at the end.

Sub MyPopulateData()

    Dim lr As Long
    Dim r As Long
    Dim nr As Long
    Dim ct As Long
   
    Application.ScreenUpdating = True
   
'   Set initial value of new row to 2
    nr = 2
   
'   Find last row in column A with data
    lr = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Loop through all rows in column C starting with row 2
    For r = 2 To lr
'       Get count from column H
        ct = Cells(r, "D")
'       See if value in column H is greater than 0
        If ct > 0 Then
'           Populate column R
            Range(Cells(nr, "E"), Cells(nr + ct - 1, "E")) = Cells(r, "A")
'           Increment next row counter
            nr = nr + ct
           
'   Find last row in column G with data
    lr = Cells(Rows.Count, "G").End(xlUp).Row
   
'       Get count from column J
        ct = Cells(r, "J")
'       See if value in column H is greater than 0
        If ct > 0 Then
'           Populate column K
            Range(Cells(nr, "K"), Cells(nr + ct - 1, "K")) = Cells(r, "G")
'           Increment next row counter
            nr = nr + ct
        End If
    Next r
   
    Application.ScreenUpdating = True
   
End Sub

Thank you and God bless,
Craig

 

[Joe4]

The error message is a bit of a "red herring". The issue is not with the "Next", but rather due to the fact that you have two "IF" statements but only only "END IF".
Every "IF" (where the entire statement is not on one line) needs a corresponding "END IF".

This can be seen much more easily if you use Code tags when posting your code, as that should maintain the format of your code (if you have indented it properly):

Sub MyPopulateData()

Dim lr As Long
Dim r As Long
Dim nr As Long
Dim ct As Long

Application.ScreenUpdating = True

' Set initial value of new row to 2
nr = 2

' Find last row in column A with data
lr = Cells(Rows.Count, "A").End(xlUp).Row

' Loop through all rows in column C starting with row 2
For r = 2 To lr
    ' Get count from column H
    ct = Cells(r, "D")
    ' See if value in column H is greater than 0
    If ct > 0 Then
        ' Populate column R
        Range(Cells(nr, "E"), Cells(nr + ct - 1, "E")) = Cells(r, "A")
        ' Increment next row counter
        nr = nr + ct

        ' Find last row in column G with data
        lr = Cells(Rows.Count, "G").End(xlUp).Row

        ' Get count from column J
        ct = Cells(r, "J")
            ' See if value in column H is greater than 0
            If ct > 0 Then
                ' Populate column K
                Range(Cells(nr, "K"), Cells(nr + ct - 1, "K")) = Cells(r, "G")
                ' Increment next row counter
                nr = nr + ct
            End If
Next r

Application.ScreenUpdating = True

End Sub

So in the future, it would be a great help to us if you could post your code using the Code tags as described here: How to Post Your VBA Code
That will save us from having to copy/paste your code on our side and re-format it all manually to see where you went wrong.
If formatted properly, it can be seem pretty quickly where the issue is.

 

[craigwojo]

Thank you again so much. Have a great rest of your day.

Thank you and God bless,
Craig

 

[Joe4]

You are welcome.