CODEWITHSUNDEEP
vbaexcel
Rich W
Rich W

Reputation: 3

Next Without For error in nested loop in Excel VBA

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 i As Integer, j As Integer
    Dim workzone As Range 'The Cell being evaluated 
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

'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

Of course the ***s are not actually in there. Any thoughts on how I can accomplish this? Thanks in advance

Upvotes: 0

Views: 7981

Answers (3)

Tim Williams
Tim Williams

Reputation: 166885

For i = 1 to 10        
   For j = 1 to 3

     Something with .Cells(i,j) 

     Set rngX = .Find(thing)           
     If Not rngX Is Nothing Then

       Set rngY = .Find(thingelse)
       If Not rngY Is Nothing Then
          'something with rngX and rngY
       End If 

     End if

   Next j    
Next i

Upvotes: 7

tpascale
tpascale

Reputation: 2576

Exit For terminates the current for loop early (the inner one in your case).

Upvotes: 1

Bruno Leite
Bruno Leite

Reputation: 1477

Use 

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

Upvotes: 1

Related Questions