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.

 

[Fluff]

You need to move the counter increment inside the If like

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

 

[davesexcel]

If your txtBox is blank, your variable still increases, you need to increase the variable only when you have entered a value

 

[futureme]

If your txtBox is blank, your variable still increases, you need to increase the variable only when you have entered a value

Duh.....yeah I should have thought of that. Still, the values are out of order when more than 3 textboxes are used. Any idea what makes that happen?

 

[Fluff]

the code loops through the controls in the order they were created, which may not be the order they appear on the form.

 

[futureme]

the code loops through the controls in the order they were created, which may not be the order they appear on the form.

OK, thanks, I thought it was probably something like that. So there's must be an internal ID. Since they're all in a column I created one then copied to make two then copied two to make 4 then copied the 4 to make 8 and so on. That would make them out of order.

Luckily I'm just starting this and it's a simple form so I'll just start over so the boxes are made in order.

 

[Fluff]

Are the textboxes named like TxtName1 TxtName2 etc?

 

[futureme]

Are the textboxes named like TxtName1 TxtName2 etc?

Yes

 

[Fluff]

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

 

[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

Great, thanks. I knew you could do that and was about to look around for that code.