VBA code for data entry in the same row multiply times

user_14345499 0
Viewed 37

I write this code for user form to enter data in the same row but different times and it gives (Invalid qualifier), date was specified in the rows

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
userform vba excel
1 Answers

Match Data Using 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
user_98140690
Related