Confused by for/next loops

[Mike Boynton via OfficeKB.com]

I have a userform with a textbox to put a starting number(txtStart) and a
textbox to put an ending number(txtEnd). I also have 30 other textboxes
(Textbox1, Textbox2, etc) that I need to loop thru to see if there is a value
in the textbox that matches the next number in the first for/next loop and
append some text to it if it does. I can get it to do this with one number,
but if I have multiple numbers and only does the one. Here is the code I
have written so far, please help...these for/next loops confuse me.

Sub Routes()
Dim MyStart As String
Dim MyEnd As String
Dim ctl As Control
Dim RouteNum

MyStart = frmRoutes.txtStart.Value
MyEnd = frmRoutes.txtEnd.Value

ActiveSheet.Range("A1").Select
For i = MyStart To MyEnd
For Each ctl In frmRoutes.Controls
If TypeOf ctl Is msforms.TextBox Then
If Not ctl.Name = "txtStart" Then
If Not ctl.Name = "txtEnd" Then
If Trim(ctl.Text) = Trim(i) Then
ActiveCell.Value = i & "A"
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = i & "B"
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
Exit For
End If
End If
End If
End If
Next ctl
Next i

Unload frmRoutes
ActiveSheet.cmdAssignRoutes.Visible = True
End Sub

 

[Tom Ogilvy]

this would be my guess at what you want:

Sub Routes()
Dim bFound as Boolean
Dim MyStart As String
Dim MyEnd As String
Dim ctl As Control
Dim RouteNum
Dim i as Long

MyStart = frmRoutes.txtStart.Value
MyEnd = frmRoutes.txtEnd.Value

ActiveSheet.Range("A1").Select
For i = MyStart To MyEnd
bFound = False
For Each ctl In frmRoutes.Controls
If TypeOf ctl Is msforms.TextBox Then
If Not (ctl.Name = "txtStart" Or _
ctl.Name = "txtEnd") Then
If Trim(ctl.Text) = Trim(i) Then
ActiveCell.Value = i & "A"
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = i & "B"
ActiveCell.Offset(1, 0).Select
bFound = True
Exit For
End If
End If
End If
Next ctl
If Not bFound Then
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
End If
Next i

Unload frmRoutes
ActiveSheet.cmdAssignRoutes.Visible = True
End Sub

 

[Mike Boynton via OfficeKB.com]

You sir are a guru...thanks you very much for the help. That worked
perfectly, I will study this code and learn from it.

this would be my guess at what you want:

Sub Routes()
Dim bFound as Boolean
Dim MyStart As String
Dim MyEnd As String
Dim ctl As Control
Dim RouteNum
Dim i as Long

MyStart = frmRoutes.txtStart.Value
MyEnd = frmRoutes.txtEnd.Value

ActiveSheet.Range("A1").Select
For i = MyStart To MyEnd
bFound = False
For Each ctl In frmRoutes.Controls
If TypeOf ctl Is msforms.TextBox Then
If Not (ctl.Name = "txtStart" Or _
ctl.Name = "txtEnd") Then
If Trim(ctl.Text) = Trim(i) Then
ActiveCell.Value = i & "A"
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = i & "B"
ActiveCell.Offset(1, 0).Select
bFound = True
Exit For
End If
End If
End If
Next ctl
If Not bFound Then
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
End If
Next i

Unload frmRoutes
ActiveSheet.cmdAssignRoutes.Visible = True
End Sub

I have a userform with a textbox to put a starting number(txtStart) and a
textbox to put an ending number(txtEnd). I also have 30 other textboxes

[quoted text clipped - 38 lines]

ActiveSheet.cmdAssignRoutes.Visible = True
End Sub