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

NessPJ

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

seti69

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
Hi,
you do have two Next in your code for only one For.
 

NessPJ

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365
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".

VBA Code:
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

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
You also have one End If missing.
 

NessPJ

Active Member
Joined
May 10, 2011
Messages
395
Office Version
  1. 365

ADVERTISEMENT

You also have one End If missing.

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,844
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 27, 2020
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,020
Messages
5,545,535
Members
410,690
Latest member
navneetr
Top