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"

[MichelleCouden1]

That has no help!!

[MichelleCouden1]

Hey Ken, I am getting a Compile error: "For Each control variable on arrays must be Variant". I had to declare your myElement cause that was the first error. I claimed it as Integer. What else would you put it as?

       ElseIf 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
   
    End If
    
    For Each myElement In myArray
        cboDistrict.AddItem myElement
    Next
End Sub

[KenBuja]

I left out one statement

    dim myArray() as String
    dim myElement as Variant

    cboDistrict.Clear

    If cboStations.Text = "Urban" Then
       etc.
    End If           

    For Each myElement in myArray
        cboDistrict.AddItem myElement     
    Next

[MichelleCouden1]

I feel like were getting somewhere, which is better than what I was getting. I am not typing the Array line properly it is giving me a Type mismatch error. It's on the myArray line for all of them.

 Dim myArray() As String
      Dim myElement As Variant
      cboDistrict.Clear
      
      If cboStations.Text = "Annual" Then
         If cboYear.Text = "2010" Then
            myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum")
         ElseIf cboYear.Text = "2011" Then
             myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum")
         ElseIf cboYear.Text = "2012" Then
              myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum")
         ElseIf cboYear.Text = "2012" Then
               myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum")
         ElseIf cboYear.Text = "2013" Then
               myArray = Array()
         ElseIf cboYear.Text = "2014" Then
               myArray = Array()
       End If
      
       ElseIf 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
   
    End If
    
    For Each myElement In myArray
        cboDistrict.AddItem myElement
    Next
     

[MichelleCouden1]

Hey Ken, where are ya??????

[KenBuja · Accepted solution]

Try this. This will change the values of your districts if you select a new year or a new station.

Private Sub UserForm_Initialize()      Dim x As Integer      For x = 2010 To 2015         cboYear.AddItem x     Next         cboStations.AddItem "Annual"     cboStations.AddItem "Urban"      End Sub  Private Sub cboStations_Change()      If cboYear.Value <> "" Then         UpdateDistrict     End If      End Sub  Private Sub cboYear_Change()          If cboStations.Value <> "" Then         UpdateDistrict     End If      End Sub  Private Sub UpdateDistrict()      Dim myArray()     Dim myElement As Variant     cboDistrict.Clear          myArray = Array()          If cboStations.Text = "Annual" Then       If cboYear.Text = "2010" Or cboYear.Text = "2011" Or cboYear.Text = "2012" Then          myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum") '      ElseIf cboYear.Text = "2011" Then '         myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum") '      ElseIf cboYear.Text = "2012" Then '          myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum") '      ElseIf cboYear.Text = "2012" Then '           myArray = Array("Abilene", "Amarillo", "Atlanta", "Austin", "Beaumont", "Brownwood", "Bryan", "Childress", "Corpus_Christi", "Dallas", "El_Paso", "Fort_Worth", "Houston", "Laredo", "Lubbock", "Lufkin", "Odessa", "Paris", "Pharr", "San Angelo", "San_Antonio", "Tyler", "Waco", "Wichita_Falls", "Yoakum") '      ElseIf cboYear.Text = "2013" Then '           myArray = Array() '      ElseIf cboYear.Text = "2014" Then '           myArray = Array()       End If          ElseIf 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") '        ElseIf cboYear.Text = "2013" Then '            myArray = Array() '        ElseIf cboYear.Text = "2014" Then '            myArray = Array()         End If     End If          For Each myElement In myArray         cboDistrict.AddItem myElement     Next  End Sub 

[MichelleCouden1]

Can't do the years like that because Urban only has the years of 2010, 2011, and 2012. I'll change them back. The code you sent me though still did not fix the "Type Mismatch" error that I am getting on the myArray = Array("Abilene", and so on...
That error is coming up on all myArray lines of code.
All we have to do is fix that error and I think it will work.

[KenBuja]

This was the code I tested in an ArcGIS VBA project which worked correctly. Did you notice the change in the line

Dim myArray()

Can't do the years like that because Urban only has the years of 2010, 2011, and 2012.

This was the code you originally had in your attached text file which populated the years from 2010 through 2015.

[MichelleCouden1]

My bad I missed that. I changed it. Oh my god,  Ken we are so close. Thank You so much, you are the only one that has gotten this to work. Thanks again. OK, let me see if I can explain this right. When I open the program and pick Annual all the years and districts go in the boxes. Perfect! When I open the program and pick Urban it loads all the years and proper districts. Perfect! The only thing is once I select Annual and then try to change my stations to Urban without closing out the program I get an error of "For loop not initialized" and then it highlights the For Each myElement line of code. IS there anyway to make it where the user can change their mind with the program open?

[LeoDonahue]

Thank You so much, you are the only one that has gotten this to work.

Ken is the only one willing to help you at the moment.  Go easy on him. 

By chance are you going to send Ken a nice thank you or something like that?  He's done alot of work for you lately.  None of his posts are helpful in any way?