Next Without For error (again)

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
121
Right, once again I'm struggling with the Next without For error. I've got a loop ("i") which if a certain condition is met (d2 > d) I want to terminate immediately and return to the beginning of the If statement.


If d2 isn't greater than I want the loop to continue until the Next command further down but I keep getting error command.


Am I missing something obvious as this isn't the first time I've had problems with this loop.


Code:
r = Cells(Rows.Count, 1).End(xlUp).Row - 2


For i = 3 To r + 2


' determine the cell to get lookup value from
v = Cells(i, 1).Value


Ptrue = Application.WorksheetFunction.VLookup(v, Sheets("SAP").Range("sapstock"), 4, False)


sapstock = Cells(i, 4)


lookup1 = 0 ' make sure variable is reset from previous loops


lookup1 = Application.VLookup(v, Range("allstock"), 4, False)


d = Now
d2 = Cells(i, 5) ' if the date is in the future I need the loop to go back to the beginning.


If d2 > d Then


Next


ElseIf lookup1 = sapstock Then
    Cells(i, 6).Value = "Quantity Is Correct"
    Cells(i, 7).Value = "" ' used to blank cells if re-running check
    Cells(i, 8).Value = ""


Else
 
Cells(i, 6).Value = "Incorrect Quantity"


Cells(i, 7).Value = lookup1 - sapstock


End If


Next i
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
Right, once again I'm struggling with the Next without For error. I've got a loop ("i") which if a certain condition is met (d2 > d) I want to terminate immediately and return to the beginning of the If statement.


If d2 isn't greater than I want the loop to continue until the Next command further down but I keep getting error command.


Am I missing something obvious as this isn't the first time I've had problems with this loop.


Rich (BB code):
For i = 3 To R + 2
  ' determine the cell to get lookup value from
  v = Cells(i, 1).Value
  Ptrue = Application.WorksheetFunction.VLookup(v, Sheets("SAP").Range("sapstock"), 4, False)
  sapstock = Cells(i, 4)
  lookup1 = 0 ' make sure variable is reset from previous loops
  lookup1 = Application.VLookup(v, Range("allstock"), 4, False)
  d = Now
  d2 = Cells(i, 5) ' if the date is in the future I need the loop to go back to the beginning.
  If d2 > d Then
Next
ElseIf lookup1 = sapstock Then
    Cells(i, 6).Value = "Quantity Is Correct"
    Cells(i, 7).Value = "" ' used to blank cells if re-running check
    Cells(i, 8).Value = ""
Else
Cells(i, 6).Value = "Incorrect Quantity"
Cells(i, 7).Value = lookup1 - sapstock
End If
Next i
The For and Next that I highlighted in blue appear to go together (given the "i" variable reference), so what is the Next statement i highlighted in red there for?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
You cannot "Exit" a loop in that manner.
What do you mean by
I want to terminate immediately and return to the beginning of the If statement.
that line is the beginning of the If
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
121
The For and Next that I highlighted in blue appear to go together (given the "i" variable reference), so what is the Next statement i highlighted in red there for?

The Next you've quoted (which I also tried with Next i but made no difference) is for if d2 > d then I want the loop to go back to the For i command and increase i and start the loop again as opposed to carrying on with the procedure and seeing if lookup1 = sapstock etc.

I tried using a Case Select but still no joy.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Just remove the Next that Rick highlighted in red
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
121
You cannot "Exit" a loop in that manner.
What do you mean by that line is the beginning of the If

The loop uses the variable i to go to the next line as it goes through a long list and checks each row against another row of a different spreadsheet. If a certain condition is met (d2 > d) then I don't want the sub to execute any more code but go back and increase i so it will then check the next line and so on.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

If you remove the Next, that's what it will do.
 

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
121
Just remove the Next that Rick highlighted in red

But that will hen execute the following code:

Code:
If lookup1 = sapstock Then
    Cells(i, 6).Value = "Quantity Is Correct"
    Cells(i, 7).Value = "" ' used to blank cells if re-running check
    Cells(i, 8).Value = ""
[\code]

which I don't want to if this condition is met. I want to return to the beginning increment i by one and run the code again.
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,235
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Maybe:
Code:
For i = 3 To R + 2
  ' determine the cell to get lookup value from
  v = Cells(i, 1).Value
  Ptrue = Application.WorksheetFunction.VLookup(v, Sheets("SAP").Range("sapstock"), 4, False)
  sapstock = Cells(i, 4)
  lookup1 = 0 ' make sure variable is reset from previous loops
  lookup1 = Application.VLookup(v, Range("allstock"), 4, False)
  d = Now
  d2 = Cells(i, 5) ' if the date is in the future I need the loop to go back to the beginning.
  If d2 > d Then GoTo Nx
If lookup1 = sapstock Then
    Cells(i, 6).Value = "Quantity Is Correct"
    Cells(i, 7).Value = "" ' used to blank cells if re-running check
    Cells(i, 8).Value = ""
Else
    Cells(i, 6).Value = "Incorrect Quantity"
    Cells(i, 7).Value = lookup1 - sapstock
End If
Nx:
Next i
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,578
Office Version
  1. 365
Platform
  1. Windows
But that will hen execute the following code:

Code:
If lookup1 = sapstock Then
    Cells(i, 6).Value = "Quantity Is Correct"
    Cells(i, 7).Value = "" ' used to blank cells if re-running check
    Cells(i, 8).Value = ""

which I don't want to if this condition is met. I want to return to the beginning increment i by one and run the code again.
No it wont, if d2>d the code will jump to End If
 

Watch MrExcel Video

Forum statistics

Threads
1,109,453
Messages
5,528,876
Members
409,843
Latest member
akostaki
Top