Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
confused | Excel Worksheet Functions | |||
Confused | Excel Worksheet Functions | |||
Confused | New Users to Excel | |||
confused,please help | Excel Discussion (Misc queries) | |||
confused... | Excel Programming |