Adding data from Form to empty rows in spreadsheet.

[dd]

I'm trying to add data to the last empty row in my worksheet. Using an
example from contextures which has...

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

However, my form adds data to the first empty row then when I try to add
another entry it over writes this entry.
How do I get it to write to the next row and so on?

This is what I'm using (from http://www.contextures.com/xlUserForm01.html):

Private Sub cmdAdd_Click()
Dim lRow As Long
Dim lSite As Long
Dim lElement As Long
Dim ws As Worksheet
Set ws = Worksheets("tblConditionDetails-Unlinked-19")

'find first empty row in database
lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

lSite = Me.cboSite.ListIndex
lElement = Me.cboElement.ListIndex

'check for a Site
If Trim(Me.cboSite.Value) = "" Then
Me.cboSite.SetFocus
MsgBox "Please select a Site"
Exit Sub
End If

'check for a Element
If Trim(Me.cboElement.Value) = "" Then
Me.cboElement.SetFocus
MsgBox "Please select an Element"
Exit Sub
End If

'copy the data to the database
With ws
.Cells(lRow, 2).Value = Me.cboSite.Value
.Cells(lRow, 3).Value = Me.cboElement.Value
.Cells(lRow, 4).Value = Me.txtDateRec.Value
.Cells(lRow, 5).Value = Me.txtDescrip.Value
.Cells(lRow, 6).Value = Me.chkPhoto.Value
.Cells(lRow, 7).Value = Me.chkReport.Value
.Cells(lRow, 8).Value = Me.txtDateReport.Value
.Cells(lRow, 9).Value = Me.txtComments.Value
.Cells(lRow, 10).Value = Me.chkRemedy.Value
.Cells(lRow, 11).Value = Me.txtDateRemedy.Value
End With

'clear the data
Me.cboSite.Value = ""
Me.cboElement.Value = ""
Me.txtDateRec.Value = Format(Date, "Short Date")
Me.txtDescrip.Value = ""
Me.chkPhoto.Value = False
Me.chkReport.Value = False
Me.txtDateReport.Value = Format(Date, "Short Date")
Me.txtComments.Value = ""
Me.chkRemedy.Value = False
Me.txtDateRemedy.Value = Format(Date, "Short Date")

End Sub

Regards
D Dawson

 

[dd]

Thanks Tom,

One more question regarding this UserForm

The dates appear in dd/mm/yyyy format in the form but are input as
mm/dd/yyyy format on the spreadsheet.
How do I get them to input as dd/mm/yyyy format?

Regards
Dylan

lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

should be

lRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

since you don't write any data into column 1 you should check for the last
used row in column 2 where you do write data.

 

[dd]

Thanks Tom,

I got the answer e.g
.Cells(lRow, 4).Value = FormatDateTime(Me.txtDateRec.Value, 1)

Regards
Dylan

"dd" <dd.dd> wrote in message Thanks Tom,

One more question regarding this UserForm

The dates appear in dd/mm/yyyy format in the form but are input as
mm/dd/yyyy format on the spreadsheet.
How do I get them to input as dd/mm/yyyy format?

Regards
Dylan

lRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

should be

lRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row

since you don't write any data into column 1 you should check for the last
used row in column 2 where you do write data.