Loop inside a loop or Nested Loop in VBA

[MichelleCouden1]

I need help with a loop. I need it to do 2 things and I'm not sure how to write  a double loop. Would my code look like this :

If cboStations.Text = "Urban" then
   If cboYears.Text = "2010" then
    cboDistricts.AddItem "Abilene"

[KenBuja]

Based on your question over on GIS.StackExchange and some of your previous questions, you could do something like this

Private Sub cboYear_Change()
    dim myArray() as String
    cboDistrict.Clear

    If cboStations.Text = "Urban" Then
        If cboYear.Text = "2010" Then
           myArray = Array ("Abilene", "Amarillo", "Austin", "San_Antonio", "Waco", "Wichita_Falls" )
        Elseif cboYear.Text = "2011" Then
           myArray = Array ("Beaumont", "Houston")
        Elseif cboYear.Text = "2012" Then 'or just Else
            myArray = Array ("Brownwood", "Bryan", "Childress", "Corpus_Christi", "El_Paso", Lubbock, "Odessa", "Yoakum")
        End If
    Elseif cboStations.Text = "Rural"
         If cboYear.Text = "2010" Then
           myArray = Array ("Abilene", "Waco", "Wichita_Falls" )
        Elseif cboYear.Text = "2011" Then
           myArray = Array ("Beaumont")
        Elseif cboYear.Text = "2012" Then 'or just Else
            myArray = Array ("Brownwood", Lubbock, "Odessa", "Yoakum")
        End If

    End If
     
    For Each myElement in myArray
        cboDistrict.AddItem myElement
    Next
    
End Sub

[MichelleCouden1]

OK, Ken before I write in you code and delete mine. I have a question about my code. Why would the district combobox not be clearing in this section?


If cboStations.Text = "Urban" Then
         cboYear.Clear
      cboYear.AddItem "2010"
      cboYear.AddItem "2011"
      cboYear.AddItem "2012"
        ElseIf cboYear.Text = "2010" Then
           cboDistrict.Clear
        cboDistrict.AddItem "Abilene"
        cboDistrict.AddItem "Amarillo"
        cboDistrict.AddItem "Austin"
        cboDistrict.AddItem "San_Antonio"
        cboDistrict.AddItem "Waco"
        cboDistrict.AddItem "Wichita_Falls"
           ElseIf cboYear.Text = "2011" Then
              cboDistrict.Clear
           cboDistrict.AddItem "Beaumont"
           cboDistrict.AddItem "Houston"
             ElseIf cboYear.Text = "2012" Then
                cboDistrict.Clear
             cboDistrict.AddItem "Brownwood"
             cboDistrict.AddItem "Bryan"
             cboDistrict.AddItem "Childress"
             cboDistrict.AddItem "Corpus_Christi"
             cboDistrict.AddItem "El_Paso"
             cboDistrict.AddItem "Lubbock"
             cboDistrict.AddItem "Odessa"
             cboDistrict.AddItem "Yoakum"
       End If
      

[KenBuja]

The easiest thing would be to move cboDistrict.Clear out of the loop altogether.

However, your syntax is incorrect for the decisions. The If loop will first evaluate what is selected in the cboStations control. If that is "Urban" it will add in the years then exit from the loop. It will never try to evaluate what has been selected in the cboYear control (and you don't have the line cboDistrict.Clear in that first evaluation).

You mistakenly put a / in the first CODE block. If you remove that, it will make it easier to read.

[MichelleCouden1]

OK, one more question. Where do I put the code? In cboStations_Change() or cboYear_Change().

[KenBuja]

This code would go into the cboYear_Change sub. In the cboStations_Change sub, I would image you'd have this code

cboYear.Clear

If cboStations.Text = "Urban" Then
           cboYear.AddItem "2010"
           cboYear.AddItem "2011"
           cboYear.AddItem "2012"
Elseif cboStations.Text = "Rural" Then
           cboYear.AddItem "2011"
End If

[MichelleCouden1]

OK, I am getting a type mismatch???? I will attach my whole code. See Attached. It's the way I have the Annual statement worded.

[KenBuja]

What line are you getting this on?

Also it looks like you want to combine the two If statements. If you are using both arrays to populate cboDistrict (you left off that code) then only use one array.

If cboStations.Text = "Annual" Then
   myArray = Array("Abilene", ...
Else If cboStations.Text = "Urban" Then  ' or just Else If if you won't have additional stations
   If cboYear.Text = "2010" Then
      myArray = Array("Abilene", ...
      etc
End If

For Each myElement in myArray
    cboDistrict.AddItem myElement
    Next

[MichelleCouden1]

I'm sorry Ken, I thought I included that. I'm getting this error on the Stations.Text = "Annual" etc....
Is there maybe a chance I can get the other loop to work? Because everything fills in correctly but the urban district names. It would be easier to fix. OR is it just better to change it all out.

[JasonPike]

Nested parens:
http://laughingsquid.com/jacob-appelbaum-donald-knuth-demonstrate-the-recursive-homeboys-principle/