Excel VBA

[Sigma7]

Good afternoon, I am trying to transfer information from my Excel User Form into the database table. After searching vigourously on the internet, I came across this formula to find the next empty row:

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

'Find First Empty Row in Database
iRow = ws.Cells(Rows.Count, 1).End(x1Up).Offset(1, 0).Row

I have the formula for transfering the information into the table:

'Copy the data to the database
ws.Cells(iRow, 1).Value = frmDocument.DateCompleted.Value
ws.Cells(iRow, 2).Value = frmDocument.CaseNumber.Value
ws.Cells(iRow, 3).Value = frmDocument.coboDocTypes.Value
ws.Cells(iRow, 4).Value = frmDocument.DateFiled.Value
ws.Cells(iRow, 5).Value = frmDocument.coboNames.Value

And for emptying the User Form:

'Clear the data for next entry
frmDocument.DateCompleted.Value = ""
frmDocument.CaseNumber.Value = ""
frmDocument.coboDocTypes.Value = ""
frmDocument.DateFiled.Value = ""
frmDocument.coboNames.Value = ""

End Sub

I cut and paste this formula into my VBA program; however I receive the following error:

Run-Time error '1004'
Application-defined or object-defined error

My question is what am I not seeing in this formula?

[NeoPa]

First and foremost Sigma, please review Before Posting (VBA or SQL) Code. It seems clear that there are a few problems here that should never be needed to be posted. You'll understand that better after reading the linked article. Don't worry. This is as much for your benefit as ours. There are tips in there that can help you avoid even the need to ask.

If you find that you still have problems after fixing all the items I see and point out, remember to include the line number of where the error occurs. Without this, we obviously will struggle to help you.

First Batch :

1. Line #4 appears to have a misspelled reference. Probably should say :
   

   Set ws = Worksheets("DocumentField")

2. Line #7 has either a typo or a misread reference. x1Up <> xlUp.

Second Batch :
Nothing drastically or obviously wrong here, but could be abbreviated to :

'Copy the data to the database
With frmDocument
    ws.Cells(iRow, 1) = .DateCompleted
    ws.Cells(iRow, 2) = .CaseNumber
    ws.Cells(iRow, 3) = .coboDocTypes
    ws.Cells(iRow, 4) = .DateFiled
    ws.Cells(iRow, 5) = .coboNames
End With

Third Batch :
Not sure where this code came from, but it's not too reliable (because some controls cannot take a string value). Try instead :

'Clear the data for next entry
With frmDocument
    .DateCompleted = Null
    .CaseNumber = Null
    .coboDocTypes = Null
    .DateFiled = Null
    .coboNames = Null
End With
End Sub

[Sigma7]

Thank you very much for the quick reply to my question. I will definitely read the link provided. However, line 7 is the culprit. I placed the <> in front of the code and it return a Syntax error. I literally copied this code from a youtube video "create a userform in excel part 3 of 3." Thanks for the other code abbreviations (awesome).

[NeoPa]

Sigma7:
However, line 7 is the culprit. I placed the <> in front of the code and it return a Syntax error.

I'm not sure I was clear enough. What I was saying is that you used a character '1' (one) in place of the 'l' (ell) which would have been the correct spelling. Maybe you did get it, but I didn't understand your response in that case. FYI: Such typos are easier to spot when the CODE tags are used. Never use a word processor to edit code as this sort of thing (and other similar problems) happens frequently in that situation.

Either way, I'm pleased to have helped :-)

[Mihail]

Try to understand the following code.
I think that is what you are looking for.

Option Explicit
Dim ws As Worksheet
Dim iRow As Long

Private Sub UserForm_Initialize()
    Set ws = Worksheets("DocumentFiled")
'Find First Empty Row in Database
    iRow = FirstEmptyRow
'in order to avoid recalculate it each time is pressed "cmdAdd" button
End Sub

Private Sub cmdAdd_Click()
'Copy the data to the database
    With ws
        .Cells(iRow, 1) = CDate(Me.txtDateCompleted)
        .Cells(iRow, 2) = CLng(Me.txtCaseNumber)
        .Cells(iRow, 3) = CStr(Me.cmbDocTypes)
        .Cells(iRow, 4) = CDate(Me.txtDateField)
        .Cells(iRow, 5) = CStr(Me.cmbNames)
    End With

'Prepare for the next record
    'next empty row
    iRow = iRow + 1
'Clear controls
    With Me
        .txtDateCompleted = Null
        .txtCaseNumber = Null
        .cmbDocTypes = Null
        .txtDateField = Null
        .cmbNames = Null
    End With
End Sub

Private Function FirstEmptyRow() As Long
'Find the last column in database (assuming that columns has headers)
Dim C As Long, Cmax As Long '
    Cmax = 1
    Do Until IsEmpty(ws.Cells(1, Cmax + 1))
        Cmax = Cmax + 1
    Loop
'Find the really empty row in database (where ALL fields have empty values)
    FirstEmptyRow = 1
    Do
        For C = 1 To Cmax
            If Not IsEmpty(ws.Cells(FirstEmptyRow, C)) Then
    GoTo NextRow
            End If
        Next C
    Exit Do 'First empty row is found
NextRow:
        FirstEmptyRow = FirstEmptyRow + 1
    Loop
End Function

A more elegant solution (and a lot easier too) can be based on .ControlSource property for a control.