How to transfer userform textbox value in correct column in spreadsheet-CodePudding

I am trying to create a Userform where operators will scan their "Operation" ("OperationBox" Textbox) from the work order and then their "Employee ID #" ("IDbox" Textbox) will be entered in correct column in the spreadsheet. Entered values will also show in Listbox. The name of the sheet is "Database"; the name of the Userform is "ScanForm".

I am not sure how can I do this check and then fill the spreadsheet cell with ID#. Any help would be great.
Thanks

ScanForm Database spreadsheet

Sub Submit()

'Transfer data from the form to the database

Dim sh As Worksheet
Dim iRow As Long

Set sh = ThisWorkbook.Sheets("Database")

'Utilizing iRow variable to store the next blank row number to transfer the data from Form to Database.
    iRow = [Counta(Database!A:A)   1]
    
    With sh
                 
        .Cells(iRow, 1) = ScanForm.JobBox.Value
        
            If .Cells(b, 1) = OperationBox.Value Then
                .Cells(iRow, 2) = ScanForm.IDbox.Value
            End If
            
            If OperationBox.Value = .Cells("C1").Value Then
                .Cells(iRow, 3) = ScanForm.IDbox.Value
            End If
            
            If OperationBox.Value = .Cells("D1").Value Then
                .Cells(iRow, 4) = ScanForm.IDbox.Value
            End If
                
        .Cells(iRow, 5) = ScanForm.PartBox.Value
        .Cells(iRow, 6) = ScanForm.QtyBox.Value
        .Cells(iRow, 7) = [Text(Now(), "DD-MM-YY HH:MM")]
    End With

End Sub

CodePudding user response：

Loop across the column headers for a match with OperationBox.

Option Explicit

Sub Submit1()

    'Transfer data from the form to the database
    Dim sh As Worksheet, iRow As Long, c As Long
    Dim rng As Range
    Set sh = ThisWorkbook.Sheets("Database")
    
    'Utilizing iRow variable to store the next blank row number
    'to transfer the data from Form to Database.
    iRow = sh.Cells(Rows.Count, 1).End(xlUp).Row   1
    ' extend list box
    Set rng = Range(Scanform.ListBox1.RowSource)
    Scanform.ListBox1.RowSource = rng.Resize(iRow).Address
    
    With sh
        .Cells(iRow, 1) = Scanform.JobBox.Value
     
        For c = 2 To 4
            If .Cells(1, c) = Scanform.OperationBox.Value Then
                .Cells(iRow, c) = Scanform.IDbox.Value
                Exit For
            End If
        Next
                
        .Cells(iRow, 5) = Scanform.PartBox.Value
        .Cells(iRow, 6) = Scanform.QtyBox.Value
        .Cells(iRow, 7) = [Text(Now(), "DD-MM-YY HH:MM")]
    End With
 
End Sub