Why does my For loop populate cells non-sequentially and with blank cells?

[futureme]

I have a custom form with 20 textboxes named txtName1 thru txtName20 (the tab indexes are also sequential which may be important to what I'm about to describe). Typically I'll only need to use 4-8 boxes for a particular record though. Once I populate however many text boxes that I need with data I click the Enter button and it sends the data in those cells to column A of the spreadsheet one after the other starting at the end of the spreadsheet. My code works oddly though:

i = 0
For Each allTextBoxes In Controls
        If allTextBoxes.Name Like "txtName*" Then
            If Len(allTextBoxes.Value) > 0 Then
                AddNewRecord.Offset(i, 0).Value = allTextBoxes
            End If
        End If
        i = i + 1
    Next

The odd thing is that if I only use 2 text boxes then the cells are populated like:

aaa
bbb

But if I use 3 text boxes the weirdness begins because then the cells in column A look like this:

aaa
bbb

ccc

That third line is blank. If I use four text boxes then the cells look like this:

aaa
bbb
ddd
ccc

Which has the 3rd and 4th cells data reversed. Using more textboxes just continues on with this weird behavior.

Now, for my purposes I really don't care if the cells are reversed. That doesn't affect what I'm working on. I suppose having the blank lines aren't a big problem either since I can delete those later. Still I'd like to know why this is happening and how to make it work in a way that makes sense.

 

[futureme]

In that case you can loop through them like

   For i = 1 To 10
      If Me.Controls("txtName" & i) <> "" Then
         addnewrecord.Offset(j).Value = Me.Controls("txtName" & i)
         j = j + 1
      End If
   Next i

I actually started out using something like this but I think I overcomplicated it by trying to check if Len() of the textbox was > 0 and kept getting an error putting ("txtName" & i).Value inside Len() and gave up on that approach.

 

[Fluff]

You're welcome & thanks for the feedback.