Next Without For error in nested loop in Excel VBA

Question

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

Tim Williams · Accepted Answer · 2012-02-10 02:04:46Z

7

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

edited Feb 10, 2012 at 2:04

answered Feb 10, 2012 at 1:59

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rich W Over a year ago

This approach definitely worked, so thank you very much! I guess I just feel that the logic flow of testing for NOT Nothing = True is inelegant.

Bruno Leite · Accepted Answer · 2012-02-10 00:46:46Z

1

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

answered Feb 10, 2012 at 0:46

Bruno Leite

1,47713 silver badges17 bronze badges

2 Comments

Rich W Over a year ago

I thought that is was considered poor practice to use "Goto" statements? At the same time this is a different approach that does allow for the logic flow I was thinking of.

Bruno Leite Over a year ago

good practice for me is the one that works :) and in this case would work well.

tpascale · Accepted Answer · 2012-02-10 00:50:38Z

1

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

answered Feb 10, 2012 at 0:50

tpascale

2,5766 gold badges25 silver badges39 bronze badges

1 Comment

mischab1 Over a year ago

He doesn't want to terminate the for loop, he wants to skip to the next iteration.

Collectives™ on Stack Overflow

Next Without For error in nested loop in Excel VBA

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related