excel - 在同一行中多次输入数据的 VBA 代码

Question

我为用户表单编写此代码以在同一行但不同时间输入数据，它给出(无效的限定符)，在行中指定日期

Private Sub CmdAdd_Click()

Dim RN As Long
Dim CN As Long
Dim NDate As Long

NDate = TxtDate.Value
RN = Application.WorksheetFunction.Match(NDate, Sheets("Data").Range("A7:A400"), 0)
CN = 1

Select Case CboMeal.Value
Case Breakfast
CN.Value = CN + 1
Case Lunch
CN.Value = CN + 6
Case Dinner
CN.Value = CN + 11
End Select


Sheet1.Cells(RN, CN).Value = TxtH1.Value
Sheet1.Cells(RN, CN + 1).Value = TxtC1.Value
Sheet1.Cells(RN, CN + 2).Value = TxtH2.Value
Sheet1.Cells(RN, CN + 3).Value = TxtC2.Value


End Sub 

Answer 1

使用 Application.Match 匹配数据

Private Sub CmdAdd_Click()
    
    Dim NDate As Long: NDate = TxtDate.Value
    Dim RN As Variant
    RN = Application.Match(NDate, Worksheets("Data").Range("A7:A400"), 0)
        
    If IsError(RN) Then
        MsgBox "Date not found!", vbCritical
        Exit Sub
    End If
    
    Dim CN As Long: CN = 1
    
    Select Case CboMeal.Value
    Case "Breakfast"
        CN = CN + 1
    Case "Lunch"
        CN = CN + 6
    Case "Dinner"
        CN = CN + 11
    Case Else
        MsgBox "No meal selected!", vbCritical
        Exit Sub
    End Select
    
    With Sheet1.Cells(RN, CN)
        .Value = TxtH1.Value
        .Offset(, 1).Value = TxtC1.Value
        .Offset(, 2).Value = TxtH2.Value
        .Offset(, 3).Value = TxtC2.Value
    End With
    
    MsgBox "Data written.", vbInformation

End Sub