Nested For Each Loops with a condition each

Amapola188 · Aug 22nd 2018

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.

Code

Dim lastrow As Long
    lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row
Dim myRange As Range 'Column A - needs an x
Dim rngCommercial As Range 'Column C - function needs to be match
For Each rngCommercial In sh3.Range("C1:C" & lastrow)
        If rngCommercial = "Commercial" Or "SalesOps" Then
For Each myRange In sh3.Range("A1:A" & lastrow)
        If myRange = "x" Then
           strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
        Exit For
        End If
Next rngCommercial

Display More

JonathanVH · Aug 22nd 2018

You're missing the Next for your inner loop. You have two Fors, so you need two matching Nexts.

Amapola188 · Aug 23rd 2018

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?

Code

Dim lastrow As Long
    lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row
Dim myRange As Range 'Column A - needs an x
Dim rngCommercial As Range 'Column C - function needs to be match
For Each rngCommercial In sh3.Range("C1:C" & lastrow)
        If rngCommercial = "Commercial" Or "SalesOps" Then
For Each myRange In sh3.Range("A1:A" & lastrow)
        If myRange = "x" Then
           strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
        Next myRange
        End If
Next rngCommercial

Display More

Amapola188 · Aug 23rd 2018

Maybe I should also say that I tried to start with the IF statement. But at IF For Each, vba starts screaming at me.

JonathanVH · Aug 23rd 2018

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.:

Code

Dim lastrow As Long
Dim myRange As Range 'Column A - needs an x
Dim rngCommercial As Range 'Column C - function needs to be match


  lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row
  For Each rngCommercial In sh3.Range("C1:C" & lastrow)
    If rngCommercial = "Commercial" Or "SalesOps" Then
      For Each myRange In sh3.Range("A1:A" & lastrow)
        If myRange = "x" Then
          strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
          Exit For
        End If
      Next myRange
    End If
  Next rngCommercial

Display More

JonathanVH · Aug 23rd 2018

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.:

Code

For Each rngCommercial In sh3.Range("C1:C" & lastrow)
    If (rngCommercial = "Commercial" Or "SalesOps") And rngCommercial(,-1) = "x" Then
      strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
    End If
  Next rngCommercial

Amapola188 · Aug 23rd 2018

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.

Code

Public Sub KickoffMinutes()


Dim strSMS As String
Dim strAM As String
Dim strClient As String
Dim strClientRef As String
Dim strMeetingDate As String
Dim strInvitees As String
Dim strQuestionsDue As String
Dim strContentDue As String
Dim strDueDate As String
Dim strSubmission As String
Dim strTenderOffice As String
Dim strColourChange As String
Dim strAM2 As String
Dim strPM As String
Dim strMarketing As String
Dim strIT As String
Dim strCommercial As String
Dim strCustomerService As String




Dim ThisFileName As String


'Errorcatch
    On Error GoTo Err_Handler


'Your Sheet names need to be correct in here
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim sh3 As Worksheet
    Set sh1 = Sheets("TenderData")
    Set sh2 = Sheets("MeetingInfo")
    Set sh3 = Sheets("TenderTeam")


strSMS = sh1.[B1]
strAM = sh1.[B2]
strClient = sh1.[B4]
strClientRef = sh1.[B5]
strMeetingDate = Format(sh1.[B16], "dddd, d mmmm YYYY - h:mm AM/PM")
strQuestionsDue = Format(sh1.[B21], "dddd, d mmmm YYYY")
strContentDue = Format(sh1.[B23], "dddd, d mmmm YYYY - h:mm AM/PM")
strDueDate = Format(sh1.[B24], "dddd, d mmmm YYYY - h:mm AM/PM")
strTenderFolder = sh1.[B13].Value
strTenderFolderHL = "<A HREF=""" & sh1.[B13].Value & """>Click here to access folder location.</A>"
strInvitees = sh3.[F79].Value
strSubmission = sh1.[B12].Value


'TenderOffice & Colour Coding Changes
If sh1.[B3] = "Christine" Then
    strColourChange = "Please let Christine know within 5 business days of kickoff meeting."
    strTenderOffice = "Christine"
Else
    strColourChange = "Please advise Karen ASAP"
    strTenderOffice = "Karen"
End If


'Bid Team - Commercial
Dim lastrow As Long
    lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row
Dim myRange As Range 'Column A - needs an x
Dim rngCommercial As Range 'Column C - function needs to match


For Each rngCommercial In sh3.Range("C1:C" & lastrow)
        If rngCommercial = "Commercial" Or "SalesOps" Then
            For Each myRange In sh3.Range("A1:A" & lastrow)
                If myRange = "x" Then
                    strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
                    Exit For
                End If
            Next myRange
        End If
Next rngCommercial




'AllFiles
Dim MyFile As String
Dim Counter As Long
'Folder with tender files
MyFile = Dir$(sh1.[B13].Value & "\Client Docs\1 - Original\")


' Setup directories
cDir = ActiveWorkbook.Path + "\" 'Change if required
ThisFileName = ThisWorkbook.Name


Dim objWord As Object
Set objWord = CreateObject("Word.Application")
objWord.Visible = True
objWord.Activate
objWord.Documents.Open "\\nrbusinessdata01\SMS\Groups\Sales\Tenders\Tender Templates\Kick-Off Meeting Minutes - SMSx.docx"
With objWord.ActiveDocument
    .FormFields("phSMS").Range.Text = strSMS
    .FormFields("phAM").Range.Text = strAM
    .FormFields("phClient").Range.Text = strClient
    .FormFields("phClientRef").Range.Text = strClientRef
    .FormFields("phMeetingDate").Range.Text = strMeetingDate
    .FormFields("phInvitees").Range.Text = strInvitees
    .FormFields("phContentDue").Range.Text = strContentDue
    .FormFields("phDueDate").Range.Text = strDueDate
    .FormFields("phQuestionsDue").Range.Text = strQuestionsDue
    .FormFields("phSubmission").Range.Text = strSubmission
    .FormFields("phColourChange").Range.Text = strColourChange
    .FormFields("phTenderOffice").Range.Text = strTenderOffice
    
    Do While MyFile <> ""
    .Bookmarks("phFiles").Range.InsertAfter MyFile & vbCr
    MyFile = Dir$
    Counter = Counter + 1
    Loop
    
End With


objWord.ActiveDocument.Sections(1).Footers(wdHeaderFooterPrimary).Range.Text = "Kick-Off Meeting Minutes - SMS" & strSMS & ".docx"


' Save new file
'Path and YYMM
Dim MinutePath As String
MinutePath = strTenderFolder & "\Meetings\"
Dim NewFileName As String
NewFileName = "Kick-Off Meeting Minutes - SMS" & strSMS & ".docx"
objWord.ActiveDocument.SaveAs MinutePath & NewFileName


'objWord.Quit
Set wrdDoc = Nothing
Set objWord = Nothing


Exit Sub


Err_Handler:
    MsgBox "Word caused a problem. " & Err.Description, vbCritical, "Error: " _
            & Err.Number
    If WordWasNotRunning Then
        Set wrdDoc = Nothing
        Set objWord = Nothing
        oWord.Quit
    End If


End Sub

Display More

Amapola188 · Aug 23rd 2018

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.

Code

Dim lastrow As Long
    lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row
Dim rngCommercial As Range 'Column C - function needs to match


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

Amapola188 · Aug 23rd 2018

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.

Code

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

JonathanVH · Aug 23rd 2018

Good catch. I missed that in my analysis.

Amapola188 · Aug 23rd 2018

Thanks for your help. I always find it useful because it makes me look at things from a different angle. So very grateful for this community.

Nested For Each Loops with a condition each

Participate now!

Share