Unload data from user form to spreadsheet in sequential order.

isaacv22

New Member
Joined
Sep 30, 2021
Messages
48
Office Version
  1. 365
Platform
  1. Windows
Hello,

I'm currently trying to have the data filled in the multi pages on userform1 unload the data sequential order based of the global variable labeled "track" however, when trying to run through the code I get the error Type mismatch on the code For n = 1 To track Step 1. I have track as a Public variant and it is the value of an input box when Userform1.CB1 is selected. I'm trying to use that input to unload the data from the userform1 to the spreadsheet in sequential order one row after another. Any idea what I'm
VBA Code:
Sub Save()

    Dim sh As Worksheet
    
    Dim iRow As Long
    
    Dim n As Long
  
    Dim track As String, X As Long
    
    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
        
        .Cells(iRow, 1) = Userform1.txtConveyor.Value
        
        .Cells(iRow, 2) = Userform1.CBMaster.Value
    
    
    If Userform1.CheckBox1 = False Then
      
        
        .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
        
    
        
        
    'FOR MULTI TRACKS
        
    ElseIf Userform1.CheckBox1 = True Then
    
    
        For n = 1 To track Step 1
        
        .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
    
    Next n
    

End If

    End With
        
 End Sub
doing wrong or missing? Any help is much appreciated. Below is the code that I am trying to run.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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
Back
Top