CODEWITHSUNDEEP
excelvbaif-statement
Aiden
Aiden

Reputation: 3

How to fix a 'Next with no For' error sent in excel vba

I'm setting up an automated calendar that should draw a name from varName() when the corresponding date in varDate() appears in the calendar(Fday is the calendar date). I am receiving confusingly getting the "Next without For" error in the loop.

The aim is to have a dynamic array for name(varName) and date(varName) where the name can be drawn into a calendar day with the corresponding date. I have succeeded in drawing a name into a calendar according to the corresponding date using a static array however when I use dynamic arrays it is giving me problems. 

stRow = Row with calendar date
stCol = Column with calendar date
nameRow = Row containing persons name (below calendar date)

    For i = LBound(varDate) To UBound(varDate)
      If Cells(stRow + 1, stCol) = Empty Then
       nameRow = stRow + 1
       If Fday = varDate(i) Then
        csheet.Cells(nameRow, stCol) = varName(i)
       End If
   Else
     nameRow = nameRow + 1  
     If Fday = varDate(i + 1) Then
       csheet.Cells(nameRow, stCol) = varName(i + 1)
     End If
    Next i

Upvotes: 0

Views: 83

Answers (2)

Benjamin Connell
Benjamin Connell

Reputation: 13

Whenever I have this error, it means I'm actually missing an 'End If' somewhere. Excel thinks the Next statement is inside the IF block because it hasn't found an End If, but there's no For in that IF block!

So that's the reason you get the error.

Damian has spotted where the missing End If should go and put it back in. He's right that proper indention makes these errors very easy to spot.

Upvotes: 0

Damian
Damian

Reputation: 5174

You should indent your code... that way you will avoid this error:

stRow = Row with calendar date
stCol = Column with calendar date
nameRow = Row containing persons name (below calendar date)

For i = LBound(varDate) To UBound(varDate)
    If Cells(stRow + 1, stCol) = Empty Then
        nameRow = stRow + 1
        If Fday = varDate(i) Then
            csheet.Cells(nameRow, stCol) = varName(i)
        End If
    Else
        nameRow = nameRow + 1
        If Fday = varDate(i + 1) Then
            csheet.Cells(nameRow, stCol) = varName(i + 1)
        End If 'you missed this one
    End If
Next i

Upvotes: 5

Related Questions