NessPJ
Active Member
- Joined
- May 10, 2011
- Messages
- 418
- Office Version
- 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?
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