Nested For Each Loops with a condition each

  • Good Morning - I know my brain would end up in knots trying to solve that one. Nested For loops is scary enough but for added piquancy I need to check out two conditions.

    I have a long list of people. Their name is in Column B. In Column C is their function. I have a formula in Column A that puts an x into the cell if the region matches another criteria. Now I'm trying to add all people that have an x in column A and match the function in column C.

    If I'm trying to step my brain through this, it should work but I get an error "Invalid Next control variable reference".

    Any suggestions very much appreciated. Thanks.

  • Hi Johnathan

    Thanks for that. I exit the For statement. My reasoning is that if I have checked that is has the x, I'm done. I don't need it to loop further through column A?

    But if I change the code to below, i.e. inserting a Next, I get an error message Next without For. I would understand that as that I have to close one loop before starting another? But I need to check the second condition before it can move on in the first loop. Does that make sense?

  • Now you don't have the inner If condition closed. You can exit the For loop when you find your value, but the loop needs to close (and return to the For clause) for any previous values (even if there are none).
    I encourage you to format your code by indenting to see where anything opens and closes, e.g.:

  • I just analyzed your code for logic rather than just syntax (which was causing the errors) and it doesn't compute. I doubt you want myRange to loop until the last row. If you're just looking to add names where both conditions are true, you don't need (or want) an inner loop: just check what's in the row's A column and add the name if it's an X; e.g.:

    1. For Each rngCommercial In sh3.Range("C1:C" & lastrow)
    2. If (rngCommercial = "Commercial" Or "SalesOps") And rngCommercial(,-1) = "x" Then
    3. strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
    4. End If
    5. Next rngCommercial
  • It sounds like vba is as confused as I am. Because the error message was about a For without a Next. It didn't complain about an open IF statement. - And now the error message says Type mismatch. It also says the issue is with Word. I'm posting the complete code here. I'm gathering all sort of information in the Excel spreadsheet and the plug into a Word Document. At the above stage (and that's where the error is), I'm not talking to Word yet.

  • I've changed my code around, thinking I might not need to nest a second For loop. Now I'm using Offset to get to the first column to test it and write the If statement with AND and OR to get all the conditions in. But I still get the type mismatch error on the If line.

    All my variables are declared, rngCommercial is a Range, strCommercial is a String and last row Long (the result should be 80). For the life of me, I can't see where there could be a mismatch.

    1. Dim lastrow As Long
    2. lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row
    3. Dim rngCommercial As Range 'Column C - function needs to match
    4. For Each rngCommercial In sh3.Range("C1:C" & lastrow)
    5. [COLOR=#FF0000] If (rngCommercial = "Commercial" Or "SalesOps") And rngCommercial.Offset(0, -2) = "x" Then[/COLOR]
    6. strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
    7. End If
    8. Next rngCommercial
  • It's working now. The error was that the OR statement needs the object repeated. - I haven't solved the issue of successfully nesting a loop but this is much cleaner anyway.

    1. For Each rngCommercial In sh3.Range("C1:C" & lastrow)
    2. If (rngCommercial = "Commercial" Or [COLOR=#FF0000]rngCommercial[/COLOR] = "SalesOps") And rngCommercial.Offset(0, -2).Value = "x" Then
    3. strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & " "
    4. End If
    5. Next rngCommercial