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
Similar Threads
