VBA - Can not close my For Each Loop with If/Else ?

[NessPJ]

Hello all,

I have a VBA routine where the debugger keeps telling me that i am using a Next statement without a For present. But it is actually there.
Did i create a structural error somewhere, so the code is no longer 'seeing' the For statement?

Private Sub Process()

Dim CheckTEXT1 As Long
Dim TEXT1 As String, TEXT2 As String
Dim TEXT1LR As Long, TEXT2LR As Long       'LR = LastRow
Dim MsgNr As String, MsgType As String, WCSMsgNr As String, SenRec As String, DateTime As String, Giftnumber As String, Location As String

MsgType = Sheets("Parameters").Range("B76").Value
SenRec = Sheets("Parameters").Range("B77").Value

TEXT1LR = Sheets("i25").Range("A65534").End(xlUp).Row

'Set variables used for all interfaces to process
    Set Rng = Sheets("i25").Range("A1:A" & TEXT1LR)
 
'Loop each cell in range
    For Each Cell In Rng.Cells

    TEXT1 = Cell.text
   
    'HEADER1
    CheckTEXT1 = InStr(1, Cell.text, "HEADER1-TEXT1", vbTextCompare)       'Check if the Message is of the right type

    If CheckTEXT1 > 0 Then
   
    DateTime = Format(Now - TimeSerial(0, 0, 1), "YYYYMMDDHHMMSS")      'Now (huidige datum/tijd) minus 1 seconde
   
    MsgNr = Mid(Cell.text, 13, 8)       
   
   
    WCSMsgNr = Mid(Cell.text, 21, 8)
    Giftnumber = Mid(Cell.text, 49, 9)
    Location = WorksheetFunction.text(Mid(Cell.text, 58, 8), "00000000")
   
    'Create HBST-TEXT2 message
    TEXT2 = MsgType & MsgNr & WCSMsgNr & SenRec & DateTime & Giftnumber & Location & "000"
    'This message is padded with 3 zeroes at the end for some reason
   
    TEXT2LR = Sheets("i22").Range("A65534").End(xlUp).Row + 1
   
        If IsEmpty(Sheets("i22").Range("A1").Value) = True Then
   
        Sheets("i22").Range("A1").Value = TEXT2
   
        Else
       
        Sheets("i22").Range("A" & TEXT2LR).Value = TEXT2
       
        End If
       
        Sheets("i25").Range(Cell.Address).Font.Color = -11489280
   
   
    Next
   
    Else        'If/Else HEADER1/HEADER2
   
    'HEADER2
    CheckTEXT1 = InStr(1, Cell.text, "HEADER2-TEXT1", vbTextCompare)       'Check if the Message is of the right type
   
    If CheckTEXT1 > 0 Then
   
    DateTime = Format(Now - TimeSerial(0, 0, 1), "YYYYMMDDHHMMSS")      'Now (huidige datum/tijd) minus 1 seconde
   
    MsgNr = Mid(Cell.text, 13, 8)       'Takes original messagenumber from message
   
   
    WCSMsgNr = Mid(Cell.text, 21, 8)
    Giftnumber = Mid(Cell.text, 49, 9)
    Location = WorksheetFunction.text(Mid(Cell.text, 58, 6), "000000")
   
    'Create HBST-TEXT2 message
    TEXT2 = MsgType & MsgNr & WCSMsgNr & SenRec & DateTime & Giftnumber & Location & "000"
    'This message is padded with 3 zeroes at the end for some reason
   
    TEXT2LR = Sheets("i22").Range("A65534").End(xlUp).Row + 1
   
        If IsEmpty(Sheets("i22").Range("A1").Value) = True Then
   
        Sheets("i22").Range("A1").Value = TEXT2
   
        Else
       
        Sheets("i22").Range("A" & TEXT2LR).Value = TEXT2
       
        End If
       
        Sheets("i25").Range(Cell.Address).Font.Color = -11489280
   
    End If
   
    Next


End Sub

 

[seti69]

Hi,
you do have two Next in your code for only one For.

 

[NessPJ]

Hi,
you do have two Next in your code for only one For.

Hello,

If i change the code like this (i had it this way in the first place), i receive exactly the same error "For Without Next".

Private Sub Process()

Dim CheckTEXT1 As Long
Dim TEXT1 As String, TEXT2 As String
Dim TEXT1LR As Long, TEXT2LR As Long       'LR = LastRow
Dim MsgNr As String, MsgType As String, WCSMsgNr As String, SenRec As String, DateTime As String, Giftnumber As String, Location As String

MsgType = Sheets("Parameters").Range("B76").Value
SenRec = Sheets("Parameters").Range("B77").Value

TEXT1LR = Sheets("i25").Range("A65534").End(xlUp).Row

'Set variables used for all interfaces to process
    Set Rng = Sheets("i25").Range("A1:A" & TEXT1LR)
 
'Loop each cell in range
    For Each Cell In Rng.Cells

    TEXT1 = Cell.text
   
    'HEADER1
    CheckTEXT1 = InStr(1, Cell.text, "HEADER1-TEXT1", vbTextCompare)       'Check if the Message is of the right type

    If CheckTEXT1 > 0 Then
   
    DateTime = Format(Now - TimeSerial(0, 0, 1), "YYYYMMDDHHMMSS")      'Now (huidige datum/tijd) minus 1 seconde
   
    MsgNr = Mid(Cell.text, 13, 8)       
   
   
    WCSMsgNr = Mid(Cell.text, 21, 8)
    Giftnumber = Mid(Cell.text, 49, 9)
    Location = WorksheetFunction.text(Mid(Cell.text, 58, 8), "00000000")
   
    'Create HBST-TEXT2 message
    TEXT2 = MsgType & MsgNr & WCSMsgNr & SenRec & DateTime & Giftnumber & Location & "000"
    'This message is padded with 3 zeroes at the end for some reason
   
    TEXT2LR = Sheets("i22").Range("A65534").End(xlUp).Row + 1
   
        If IsEmpty(Sheets("i22").Range("A1").Value) = True Then
   
        Sheets("i22").Range("A1").Value = TEXT2
   
        Else
       
        Sheets("i22").Range("A" & TEXT2LR).Value = TEXT2
       
        End If
       
        Sheets("i25").Range(Cell.Address).Font.Color = -11489280
   
   
    Else        'If/Else HEADER1/HEADER2
   
    'HEADER2
    CheckTEXT1 = InStr(1, Cell.text, "HEADER2-TEXT1", vbTextCompare)       'Check if the Message is of the right type
   
    If CheckTEXT1 > 0 Then
   
    DateTime = Format(Now - TimeSerial(0, 0, 1), "YYYYMMDDHHMMSS")      'Now (huidige datum/tijd) minus 1 seconde
   
    MsgNr = Mid(Cell.text, 13, 8)       'Takes original messagenumber from message
   
   
    WCSMsgNr = Mid(Cell.text, 21, 8)
    Giftnumber = Mid(Cell.text, 49, 9)
    Location = WorksheetFunction.text(Mid(Cell.text, 58, 6), "000000")
   
    'Create HBST-TEXT2 message
    TEXT2 = MsgType & MsgNr & WCSMsgNr & SenRec & DateTime & Giftnumber & Location & "000"
    'This message is padded with 3 zeroes at the end for some reason
   
    TEXT2LR = Sheets("i22").Range("A65534").End(xlUp).Row + 1
   
        If IsEmpty(Sheets("i22").Range("A1").Value) = True Then
   
        Sheets("i22").Range("A1").Value = TEXT2
   
        Else
       
        Sheets("i22").Range("A" & TEXT2LR).Value = TEXT2
       
        End If
       
        Sheets("i25").Range(Cell.Address).Font.Color = -11489280
   
    End If
   
    Next


End Sub

 

[seti69]

You also have one End If missing.

 

[NessPJ]

You also have one End If missing.

Can you please clarify? For Each statement, does not need an End statement?

 

[Joe4]

The number of "Next" statements must match the number of "For" statements.
Unless you have single line "If ... Then" statements, the number of "End If" statements must match the number of "If ... Then" statements.

If you have any sort of imbalance, you will get the kind of error you are seeing.
So go through your code, and make sure all those counts match up.
If you properly indent your code, this will be easier to see also.

 

[seti69]

Can you please clarify? For Each statement, does not need an End statement?

I added an End If at the end of your code (before the End Sub) and it worked, the message about the For ... Next loop did not apper anymore.
When you count the Ifs and the End Ifs, you see there is one If more than End If.