nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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"

Find more posts tagged with

Accepted answers

kenbuja

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

All comments

LeoDonahue

I doubt it will be the last time.

There are alot of uncredited answers throughout the forums. Just the way it goes some days.

kenbuja

I understand I always make the right comment. I will definitely be more attentive to my comments. Thanks to you both for continuing to teach me the rules and ways of the forum and of VBA. I will most certainly use the post icons.

And yet you mark your own post as the correct answer? I think you need to reread that blog.

With my status as a forum moderator (obtained by having users mark my posts as the correct answer when appropriate), I have gone ahead and changed the correct answer indicator since you are unwilling to do that.

LeoDonahue

Michelle,

Seriously, I think people like you, but stop marking your own post as the answer. You need to mark one of Ken's replies as the answer.

MichelleCouden1

I understand I always make the right comment. I will definitely be more attentive to my comments. Thanks to you both for continuing to teach me the rules and ways of the forum and of VBA. I will most certainly use the post icons.

kenbuja

I ended up getting the first helpful post in this thread and I didn't even do anything - my guilt will set in later today I'm sure.

No need for guilt, Leo...that was me marking it as helpful 🙂

LeoDonahue

Sorry Michelle,

I read some of your comments like: "Hey Ken where are you?" and "All *we* have to do is fix this.."

I understand you are trying to learn VBA, but it can appear to casual readers that you are waiting for Ken to fix your problem. Bring something to the table. And you haven't acknowledged him at all with the tool the forum provides to do so.

I ended up getting the first helpful post in this thread and I didn't even do anything - my guilt will set in later today I'm sure.

kenbuja

What Leo meant was to start marking which posts were helpful and which one actually answered your question. You often mark your own posts as the correct answer, but rarely do that for other contributors. This doesn't encourage people to help you out very much. Please take a look at the Esri blog post on the MVP program.

Note the following:

Any logged in user can use the �??arrow�?� icons on the far right to vote for any of the replies on any threads if they believe that reply contains useful information. Users may vote for more than one reply in a thread. This voting tool allows the ArcGIS user community as a whole to collectively bubble the best replies to the top of the thread. This does two things:
It allows other users later to find the most useful information most quickly without necessarily reading all of the posts in the thread.
It rewards the reply�??s author with points that contribute to their status within the community, allowing other users to identify the most helpful members of the community, building trust and encouraging collaboration.

MichelleCouden1

Leo, I'm not quite sure what you mean on thanking Ken?? I thanked him many, many times in the last post. I am always grateful for the help you guys provide me because I am new at programming and right now I need the help. The post that was of no help was from a scjpike not Ken. scjpike sent me a link and there was nothing on it that I could find to help with this program.
Ken, thank you again for helping me I really do appreciate it. I found my mistake and I have corrected it. It works beautifully. I am sorry. I missed a step that needed to be typed out. Thank You again. Hope you all have a good week.

kenbuja

Thanks Leo!

The code I provided yesterday will allow the user to change the stations and the years and get a new list.

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?

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?

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

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

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

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

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

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

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

MichelleCouden1

That has no help!!

JasonPike

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

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.

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

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.

Code3.txt.zip

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, one more question. Where do I put the code? In cboStations_Change() or cboYear_Change().

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, 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

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

Join the Conversation

Signed in members can post, follow updates, and more. New here? Register a free account.

Quick Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.