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.
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,311
Office Version
  1. 365
Platform
  1. Windows
You need to move the counter increment inside the If like
VBA Code:
   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

Well-known Member
Joined
Feb 26, 2006
Messages
1,089
If your txtBox is blank, your variable still increases, you need to increase the variable only when you have entered a value
 

futureme

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,311
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

futureme

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,311
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Are the textboxes named like TxtName1 TxtName2 etc?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,311
Office Version
  1. 365
Platform
  1. Windows
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
 

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
Great, thanks. I knew you could do that and was about to look around for that code.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,120
Messages
5,622,851
Members
415,934
Latest member
adstocking

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