Referncing data in column A to determine Row for data entry

[dtt084]

I am trying to create a User Form that will enter data into a spreadsheet. I want the user of the form to be able to enter a date as the first piece of information in the User Form, this date will then correspond with the row in which the remaining data is entered (note all relevant dates are already in the spreadsheet so I am just doing a find function to locate the date in the row corresponding to where I want the data to go)

I am very new to VBA so I am not sure that I am even approaching this the right way but here is what I have so far:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sales")

'find matching data row in database
iRow = Cells.Find(What:=Me.txtDate.Value, After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
'check for a date
If Trim(Me.txtDate.Value) = "" Then
  Me.txtDate.SetFocus
  MsgBox "Please enter a date"
  Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 5).Value = Me.txtSmall.Value
ws.Cells(iRow, 6).Value = Me.txtMedium.Value
ws.Cells(iRow, 7).Value = Me.txtLarge.Value
ws.Cells(iRow, 8).Value = Me.txtXL.Value

'clear the data
Me.txtSmall.Value = ""
Me.txtMedium.Value = ""
Me.txtLarge.Value = ""
Me.txtXL.Value = ""
Me.txtDate.SetFocus

End Sub

I'm not sure why this isn't working, I am getting an error message:

Run-time error '1004':
Application-defined or object-defined error

Not sure what it means but I did notice when I run the cursor over iRow in the ws.cells(iRow, 5) it is displaying a value of -1 which seems wrong to me.

Any help would be greatly appreciated, thanks!

[Palmetto]

Your post does not comply with the Forum Rules regarding use of code tags. All VBA code must be wrapped in code tags. After editing your post to apply the code tags solutions will be suggested.

Rule #3

Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button. For more information about these and other tags, click here.

[Palmetto]

The error occurs because you are attempting to "activate" a variable, dimensioned as long, treating it as a range object.

See this link for help on the Find Method

You can keep iRow as a Long variable and dimension a new variable as a range.

Dim rFound as range

If Find returns a result you can reference the row property of the range object and continue using iRow as you have further in the code.

If Not rfound Is Nothing Then
    iRow = rfound.Row
End If

[foxguy]

Change "Activate" to "Row"

iRow = Cells.Find(What:=Me.txtDate.Value, After:=ws.Cells(1, 1), LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Row