Multiple For loops

isaacv22

New Member
Joined
Sep 30, 2021
Messages
48
Office Version
  1. 365
Platform
  1. Windows
Hello, I'm trying to run two different for loops in the code, one for adding a new line and the other to step through the next row of information. When I try to run the code, it's only running through one loop when I need it to run through both. Any help would be appreciated.

Thanks
VBA Code:
  Sub Save()

    Dim sh As Worksheet
    
    Dim iRow As Long
    
    Dim n As Long
    
    Dim m As Long
    
    tracks = track
    
    Set sh = ThisWorkbook.Sheets("form")
    
    If Userform1.txtRowNumber.Value = "" Then       'If statement to account for editting feature

        iRow = [Counta(form!A:A)] + 1
    Else
    
        iRow = Userform1.txtRowNumber.Value
        
    End If

          
    With sh
    
    If Userform1.CheckBox1 = False Then
    
        .Cells(iRow, 1) = Userform1.txtConveyor.Value
        
        .Cells(iRow, 2) = Userform1.CBMaster.Value
        
        .Cells(iRow, 3) = Userform1.txtTrack1.Value
        
        .Cells(iRow, 4) = Userform1.txtLength1.Value
        
        .Cells(iRow, 5) = Userform1.txtElongation1.Value
        
        .Cells(iRow, 6) = Userform1.txtMaterial1.Value
        
        .Cells(iRow, 7) = Userform1.txtSeries1.Value
        
        .Cells(iRow, 8) = Userform1.txtSurface1.Value
        
        .Cells(iRow, 9) = Userform1.txtWidth1.Value
        
        .Cells(iRow, 10) = IIf(Userform1.CBIN1.Value = True, "IN", "MM") 'IN & MM Checkbox Fucntions
        
        
        If Userform1.txtSurface1.Value = "" Then
        
            .Cells(iRow, 11) = Userform1.txtMaterial1.Value & Userform1.txtSeries1.Value & Userform1.txtSurface1.Value & "-" & Userform1.txtWidth1.Value & IIf(Userform1.CBIN1.Value = True, "IN", "MM")
        
        
        ElseIf Not Userform1.txtSurface1.Value Like "*[!1-9]*" Then
        
            .Cells(iRow, 11) = Userform1.txtMaterial1.Value & (CDbl(Userform1.txtSeries1.Value) + CDbl(Userform1.txtSurface1.Value)) & "-" & Userform1.txtWidth1.Value & IIf(Userform1.CBIN1.Value = True, "IN", "MM")

            
        ElseIf Not Userform1.txtSurface1.Value Like "*[!A-Za-z]*" Then
        
            .Cells(iRow, 11) = Userform1.txtMaterial1.Value & Userform1.txtSeries1.Value & Userform1.txtSurface1.Value & "-" & Userform1.txtWidth1.Value & IIf(Userform1.CBIN1.Value = True, "IN", "MM")


        End If
    
        
        'Order of entering data on UserFrom1 and form spreadsheet
        
        'Order of Drive Sprocket information
        
        .Cells(iRow, 12) = Userform1.txtDStyle1.Value
        
        .Cells(iRow, 13) = Userform1.txtDSeries1.Value
        
        .Cells(iRow, 14) = Userform1.txtDTeeth1.Value
        
        .Cells(iRow, 15) = Userform1.txtDBore1.Value
        
        .Cells(iRow, 16) = IIf(Userform1.CBDIN1.Value = True, "IN", "MM") 'IN & MM Checkbox Track1 Drive
        
        .Cells(iRow, 17) = Userform1.txtDEngage1.Value
        
        If Userform1.CBDANA1.Value = True Then
        
            .Cells(iRow, 12) = ""
            
            .Cells(iRow, 13) = ""
            
            .Cells(iRow, 14) = ""
            
            .Cells(iRow, 15) = ""
            
            .Cells(iRow, 16) = ""
            
            .Cells(iRow, 17) = ""
            
            .Cells(iRow, 18) = "Asset Not Accessible"
            
        Else
        
            .Cells(iRow, 18) = Userform1.txtDStyle1.Value & Userform1.txtDSeries1.Value & "-" & Userform1.txtDTeeth1.Value & "T_" & Userform1.txtDBore1.Value & IIf(Userform1.CBDIN1.Value = True, "IN", "MM") & "_" & Userform1.txtDEngage1.Value
            
                    
        End If
        
        'Order of Return Sprocket information
        
        .Cells(iRow, 19) = Userform1.txtRStyle1.Value
        
        .Cells(iRow, 20) = Userform1.txtRStyle1.Value
        
        .Cells(iRow, 21) = Userform1.txtRTeeth1.Value
        
        .Cells(iRow, 22) = Userform1.txtRBore1.Value
        
        .Cells(iRow, 23) = IIf(Userform1.CBRIN1.Value = True, "IN", "MM") 'IN & MM Checkbox Track1 Drive
        
        .Cells(iRow, 24) = Userform1.txtREngage1.Value
        
        If Userform1.CBRANA1.Value = True Then
        
            .Cells(iRow, 19) = ""
            
            .Cells(iRow, 20) = ""
            
            .Cells(iRow, 21) = ""
            
            .Cells(iRow, 22) = ""
            
            .Cells(iRow, 23) = ""
            
            .Cells(iRow, 24) = ""
            
            .Cells(iRow, 25) = "Asset Not Accessible"
            
        Else
        
            .Cells(iRow, 25) = Userform1.txtRStyle1.Value & Userform1.txtRSeries1.Value & "-" & Userform1.txtRTeeth1.Value & "T_" & Userform1.txtRBore1.Value & IIf(Userform1.CBRIN1.Value = True, "IN", "MM") & "_" & Userform1.txtREngage1.Value
            
                    
        End If
        
        ElseIf Userform1.CheckBox1 = True Then
    
      [B]  For m = 1 To iRow - 1 + tracks Step 1[/B]
        
        [B]For n = 1 To tracks - 1 + n Step 1[/B]
        
        .Cells(iRow, 1) = Userform1.txtConveyor.Value
        
        .Cells(iRow, 2) = Userform1.CBMaster.Value
        
        .Cells(iRow, 3) = Userform1.Controls("txtTrack" & n).Value
        
        .Cells(iRow, 4) = Userform1.Controls("txtLength" & n).Value
        
        .Cells(iRow, 5) = Userform1.Controls("txtElongation" & n).Value
        
        .Cells(iRow, 6) = Userform1.Controls("txtMaterial" & n).Value
        
        .Cells(iRow, 7) = Userform1.Controls("txtSeries" & n).Value
        
        .Cells(iRow, 8) = Userform1.Controls("txtSurface" & n).Value
        
        .Cells(iRow, 9) = Userform1.Controls("txtWidth" & n).Value
        
        .Cells(iRow, 10) = IIf(Userform1.Controls("CBIN" & n).Value = True, "IN", "MM") 'IN & MM Checkbox Fucntions
        
        
        If Userform1.Controls("txtSurface" & n).Value = "" Then
        
            .Cells(iRow, 11) = Userform1.Controls("txtMaterial" & n).Value & Userform1.Controls("txtSeries" & n).Value & Userform1.Controls("txtSurface" & n).Value & "-" & Userform1.Controls("txtWidth" & n).Value & IIf(Userform1.Controls("CBIN" & n).Value = True, "IN", "MM")
        
        
        ElseIf Not Userform1.Controls("txtSurface" & n).Value Like "*[!1-9]*" Then
        
            .Cells(iRow, 11) = Userform1.Controls("txtMaterial" & n).Value & (CDbl(Userform1.Controls("txtSeries" & n).Value) + CDbl(Userform1.Controls("txtSurface" & n).Value)) & "-" & Userform1.Controls("txtWidth" & n).Value & IIf(Userform1.Controls("CBIN" & n).Value = True, "IN", "MM")

            
        ElseIf Not Userform1.Controls("txtSurface" & n).Value Like "*[!A-Za-z]*" Then
        
            .Cells(iRow, 11) = Userform1.Controls("txtMaterial" & n).Value & Userform1.Controls("txtSeries" & n).Value & Userform1.Controls("txtSurface" & n).Value & "-" & Userform1.Controls("txtWidth" & n).Value & IIf(Userform1.Controls("CBIN" & n).Value = True, "IN", "MM")


        End If
          
[B]    Next n
    
    Next m[/B]

End If

    End With
        
 End Sub
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,758
Office Version
  1. 2010
Platform
  1. Windows
"track" appears to be undefined so this statement:
VBA Code:
tracks = track
will set tracks to zero
so this statement:
VBA Code:
For n = 1 To tracks - 1 + n Step 1[/B]
wil not execute a loop because tracks -1 is =-1
by the way what are you expecting the +n in this statement to do because I think you will find n=0 at this point as well
 

isaacv22

New Member
Joined
Sep 30, 2021
Messages
48
Office Version
  1. 365
Platform
  1. Windows
"track" appears to be undefined so this statement:
VBA Code:
tracks = track
will set tracks to zero
so this statement:
VBA Code:
For n = 1 To tracks - 1 + n Step 1[/B]
wil not execute a loop because tracks -1 is =-1
by the way what are you expecting the +n in this statement to do because I think you will find n=0 at this point as well
tracks is equal to track since 'track' is a value based off an inputbox. Tracks is a public integer that is being utilized for 'n' since all my textboxes are labeled 1-10 it's using the value of 'n' to step through each textbox one at a time. This Loop works, I just can't seem to run For m (adds a x amount of lines based on the value of tracks) and For n (it gives the range of what text boxes will be referenced and steps it through 1 at a time) together as I need m to add the new line so the next line of code from n can be added. Not sure if this answers your question?
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
2,758
Office Version
  1. 2010
Platform
  1. Windows
your varaible m is not used in your loop so it won't do anything
maybe you need to change:
VBA Code:
.Cells(iRow, 1) = Userform1.txtConveyor.Value
to
VBA Code:
.Cells(m, 1) = Userform1.txtConveyor.Value
and everywhere else that irow appears
by the way I still don't see how you are setting "track" is this done outside of the code you have posted? is track a public variable as well?
 

Forum statistics

Threads
1,181,427
Messages
5,929,827
Members
436,697
Latest member
sunnypl

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