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

futureme

New Member
Joined
Apr 17, 2017
Messages
19
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:

VBA Code:
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

New Member
Joined
Apr 17, 2017
Messages
19
In that case you can loop through them like
VBA Code:
   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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,335
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,147
Messages
5,623,008
Members
415,946
Latest member
bellerom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top