Thread: Next without For error in nested loop - Escaping a Nested Loop?

Hi,
I am trying to figure out a way to run a Vlookup on a Cell in my "System File" by checking a table in a "New Data" File. HOWEVER, if there is an #N/A error, I want the cells' values to be unchanged. I've come up with the following, however, I keep getting a "Next without For" error. Is it possible to escape a nested For Next loop?

The tl;dr semantic version:

       For i 1 to 10
           For j 1 to 3 
               Something with .Cells(i,j) 
                Set range X = .Find(thing
               If X = Nothing Then
                 Next j *** <THIS IS WHERE MY ERROR IS THROWN
              Else
                -Do Something with X-
              End if
           Next j
       Next i

My more or less actual code is as follows:

Sub Thing()
        Dim SysWS As Worksheet
        Dim NewDataWS As Worksheet
			Dim NDSKUs As Range   ' This is set to the first column of the NewDataWS
			Dim NDMonthsRow As Range ' This is set to the first row of the NewDataWS      
		Dim SKU2look4 As String, Month2look4 As String        
            Dim ifoundtheSKU As Range 'the result of finding SKU2look4 inside of NDSKUs range
	    Dim ifoundtheDate As Range 'the result of finding Month2look4 inside of NDMonthsRow range
  	    Dim workzone As Range 'The Cell being evaluated         
         Dim i As Integer, j As Integer
	---SNIP---
    For i = 2 To SysWS.UsedRange.Columns.Count
      For j = 2 To SysWS.UsedRange.Rows.Count
         Set workzone = SysWS.Cells(j, i)
            SKU2look4 = SysWS.Cells(j, 1) 'SKUs are along the left column
            Month2look4 = SysWS.Cells(1, i) 'Dates are along the top row

'1-Find the right Date Column for extraction
		Set ifoundtheDate = NDMonthsRow.Find(What:=Month2look4, LookIn:=xlValues, _
						LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
						MatchCase:=False, SearchFormat:=False)
			If ifoundtheDate Is Nothing Then
						Debug.Print (Month2look4 & " -Date NOT Found in New Date File")
						Next j
			Else
						Debug.Print ("ifoundtheDate:" & ifoundtheDate.Address)
			End If
'2-Find the row
		Set ifoundtheSKU = NDSKUs.Find(What:=SKU2look4, LookIn:=xlValues, _
						LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
						MatchCase:=False, SearchFormat:=False)
			If ifoundtheSKU Is Nothing Then
						Debug.Print (SKU2look4 & " Not Found in the New Data File")
						Next j
			Else
						Debug.Print ("ifoundtheSKU:" & ifoundtheSKU.Address)
			End If

'3-Set the "workzone" cell's value to that of the found row offset by the found column
					workzone = ifoundtheSKU.Offset(, (ifoundtheDate.Column - 1))
        Next j
    Next i

Thanks in advance for any insight on how to accomplish this task.

Hi

If you only want to process something when there is a result, then make the if statement reflect that, and allow the loop to function normally.

Using your pseudo code approach to the if

for j = 1 to 3
set x = .find(thing)
if not X is nothing then
do whatever
end if
next j

Hopefully that will make sense.

rylo

I would take rylo's suggestion and re-write your code so you would not have to exit a loop.

--But if you have toooo ... the vba command to exit a For Loop is "Exit For" Again this is poor programming practive and leads to unmanagable code.



Ted

I guess it's the logic of testing "If Not X is Nothing" is a little weird to me in that I'll have to bury tests within one anothers' If-Then loops. Still, it works, I just don't like how the code plays out. Thanks much.