Next Without For error (again)

[steveh8204]

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.

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

 

[Rick Rothstein]

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.

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]

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]

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]

Just remove the Next that Rick highlighted in red

 

[steveh8204]

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]

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

 

[steveh8204]

Just remove the Next that Rick highlighted in red

But that will hen execute the following 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]

Maybe:

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]

But that will hen execute the following 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