将10个条目从用户表单添加到工作表

Question

我正在寻找一种方法，以缩短我的代码，以输入数据从10个条目的形式。

这是我的用户表单，其中包含一个RMA编号(适用于所有10个PN)、一个客户名称、10个部件号和10个序列号。

​

这就是我希望将数据传输到工作表的方式。

​

部件号文本框被命名为TB#。

序列号文本框被命名为SNTB#。

这是我拥有的第一个条目的代码。我正在考虑添加代码来表示"TB"&"i“和"SNTB"&"i"，但我不知道将该语句放在哪里或如何开始。

Private Sub EnterButton_Click()
                
    'this assigns receiving data to first columns of log Sheet          
                
    If TB1.Value = "" Then
                       
    Else
                
        Worksheets("RM Tracker").Activate
        Dim lastrow
                             
        lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
        lastrow = lastrow + 1
                            
        Cells(lastrow, 1) = RMATB.Value
        Cells(lastrow, 2) = CustCB.Value
        Cells(lastrow, 3) = TB1.Value
        Cells(lastrow, 4) = SNTB1.Value
        Cells(lastrow, 5) = ReceiveTB.Value
                             
        ActiveCell.Offset(1, 0).Select
                
   End If
                
   ActiveWorkbook.Save
   
   Call resetform
                
End Sub

                
Sub resetform()
                
    RMATB.Value = ""
    CustCB.Value = ""
    TB1.Value = ""
    SNTB1.Value = ""
                
    ReceiveTB = ""
                
    'sets focus on that first textbox again
    RecForm.RMATB.SetFocus     
                
End Sub

Answer 1

您可以合并一个for循环，其中"i“表示您正在处理的行。在追加数据时，需要将该引用放入循环中，以便重新计算新行。

 Private Sub EnterButton_Click()
                
                 
                'this assigns receiving data to first columns of log Sheet
                
                
                
                If TB1.Value = "" Then
                
                
                Else
                            
                            Worksheets("RM Tracker").Activate
                            dim i as long
                        For i = 1 To 10
                            
                             Dim lastrow as long ' should put a data type with dim statements
                             
                             lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
                             lastrow = lastrow + 1
                            
                             Cells(lastrow, 1) = Userform1.Controls("RMATB" & i).Value ' change userform name to fit your need
                             Cells(lastrow, 2) = Userform1.Controls("CustCB" & i).Value
                             Cells(lastrow, 3) = Userform1.Controls("TB1" & i).Value
                             Cells(lastrow, 4) = Userform1.Controls("SNTB1" & i).Value
                             Cells(lastrow, 5) = Userform1.Controls("ReceiveTB" & i).Value
                             
                       Next i
                 
                
                End If
                            
                ActiveWorkbook.Save
                
                 
                Call resetform
                
                End Sub
                
                 
                
                
        Sub resetform()
                
                
                RMATB.Value = ""
                CustCB.Value = ""
                TB1.Value = ""
                SNTB1.Value = ""
                
                ReceiveTB = ""
                
                'sets focus on that first textbox again
                RecForm.RMATB.SetFocus