Confused by for/next loops
 

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

Confused by for/next loops
 

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

--
Regards,
Tom Ogilvy

"Mike Boynton via OfficeKB.com" <u10467@uwe wrote in message
news:5ada3b26d2a78@uwe...
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1

Confused by for/next loops
 

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

Tom Ogilvy wrote:
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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200601/1