Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Programming / January 2006

Tip: Looking for answers? Try searching our database.

Confused by for/next loops

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Mike Boynton - 24 Jan 2006 21:02 GMT
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 - 24 Jan 2006 22:06 GMT
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

Signature

Regards,
Tom Ogilvy

> 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
Mike Boynton - 25 Jan 2006 13:14 GMT
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:
>
[quoted text clipped - 42 lines]
>> ActiveSheet.cmdAssignRoutes.Visible = True
>> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.