vba - 将数据形成特定单元格

Question

在 Excel sheet2 中，我有 A 和 D 列的名称、B 和 E 开始日期，C 和 F 列是结束日期和一个带有组合框(加载名称)和两个文本框的表单。

我希望当我单击提交按钮时，它会在列中搜索与组合框值匹配的名称，然后将两个文本框的值写入右侧相邻的两个空单元格

         Private Sub CommandButton4_Click()
         Dim irow As Long
         Dim ws As Worksheet
         Set ws = Worksheets("Sheet2")
         With ws
      .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Value = Me.Combo.Value
    .Cells(.Rows.Count, "B").End(xlUp).Offset(1, 0).Value = Me.sttdate.value
    .Cells(Rows.Count, "C").End(xlUp).Offset(1, 0).Value = Me.enddate.Value
      End With
    With Me
    .Combo.Value = ""
    .startdate.Value = ""
    .enddate.Value = ""
    End With
    End Sub

此代码将所有表单的值添加到 A B 和 C 列中

Answer 1

这应该可以解决问题。我根据您在解释中写的内容添加了一些检查，以防万一。

Private Sub CommandButton4_Click()

Dim irow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet2")

With ws

    irow = .Range("A" & .Rows.Count).End(xlup).Row

    Dim rFound as Range
    Set rFound = .Range("A1:A" & iRow).Find(Me.Combo.Value, lookat:=xlWhole)

    If not rFound is Nothing Then

       If IsEmpty(rFound.Offset(,1)) and IsEmtpy(rFound.Offset(,2)) Then 

           rFound.Offset(,1) = Me.sttdate.value
           rFound.Offset(,2) = Me.enddate.value

           With Me
              .Combo.Value = ""
              .startdate.Value = ""
              .enddate.Value = ""
           End With

       Else

           Msgbox "Name already has values"

       End If


   Else

       Msgbox "Name not Found"

   End If

  End Sub