如何将Excel Userform文本框的值复制到工作表的多个指定单元格中

实现逻辑：

• 先对Quantity输入框做合法性校验，避免非正整数输入引发报错
• 借助VBA的Resize属性一次性给指定行数的单元格赋值，无需循环操作

修改后代码：

Private Sub SubmitBtn_Click()
    Dim iRow As Long, C As Long
    Dim sh As Worksheet
    ' 输入合法性校验
    If Not IsNumeric(QTYBox.Value) Or Val(QTYBox.Value) < 1 Then
        MsgBox "请输入有效的正整数数量", vbExclamation
        QTYBox.SetFocus
        Exit Sub
    End If
    If TxtPanel.Value = "" Then
        MsgBox "请输入Panel ID内容", vbExclamation
        TxtPanel.SetFocus
        Exit Sub
    End If
    
    Set sh = ThisWorkbook.Sheets("Data")
    iRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row + 1
    C = Val(QTYBox.Value)
    
    ' 一次性填充指定行数
    sh.Cells(iRow, 1).Resize(C, 1).Value = TxtPanel.Value
    
    ' 提交后清空表单重置焦点
    Call UserForm_Initialize
End Sub

Private Sub UserForm_Initialize()
    '清空Panel ID输入框
    TxtPanel.Value = ""
    '清空数量输入框
    QTYBox.Value = ""
    '焦点定位到Panel ID输入框
    TxtPanel.SetFocus
End Sub

实现逻辑：

• 保留原有的双条件匹配逻辑，将日期生成替换为VBA原生Format函数，兼容性更强
• 增加匹配不到的提示，避免用户无法判断是否执行成功
• 新增输入非空校验，减少无效匹配

修改后代码：

Private Sub SubmitBtn_Click()
    Dim i As Long
    Dim sh As Worksheet
    Dim lRow As Long
    Dim matched As Boolean
    
    ' 输入非空校验
    If PanelTxt.Value = "" Or IDTxt.Value = "" Or CrateTxt.Value = "" Then
        MsgBox "请填写完整的表单内容", vbExclamation
        Exit Sub
    End If
    
    Set sh = ThisWorkbook.Sheets("Data")
    lRow = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
    matched = False
    
    With sh
        For i = 2 To lRow
            '双条件匹配
            If .Cells(i, 1).Value = PanelTxt.Value And .Cells(i, 2).Value = IDTxt.Value Then           
                '写入日期和Crate编号
                .Cells(i, 5) = Format(Now(), "DD-MM-YY HH:MM")
                .Cells(i, 6) = CrateTxt.Value
                matched = True
                Exit For
            End If
        Next
    End With
    
    ' 匹配结果提示
    If matched Then
        MsgBox "记录更新成功", vbInformation
    Else
        MsgBox "未找到匹配的Panel ID和ID组合", vbExclamation
    End If
End Sub

内容的提问来源于stack exchange，提问作者LiloK