Next Without For error (again)

steveh8204

Board Regular
Joined
Aug 20, 2018
Messages
143
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Just remove the Next that Rick highlighted in red
 
Upvote 0
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.
 
Upvote 0
If you remove the Next, that's what it will do.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top