G
Gromit
Hi,
I'm trying to get a userform to transfer some data to a sheet (called
Data). I've entered a dynamic range (called DataRange) in Data using
the standard =OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1)
All I want to do is to transfer some info from the Form to the next
empty row in Data, without selecting the Data sheet. My problem is that
when the routine first runs, this line of code correctly counts the row
number that I need:
NxtRow = Dsht.[DataRange].Rows.Count + 1
However, on subsequent clicks of the userform enter button, the new
data just overwrites the previous entry. Using a msgbox, I discovered
that the NxtRow variable, and so the DataRange.Rows.Count value, is not
updating. The full code being called by the Private Sub Enter_Click()
procedure is as follows. Thanks ever so for any help,
Graham
Private Sub Transdata()
Dim NxtRow As Long
Dim Dsht As Worksheet
Set Dsht = ActiveWorkbook.Worksheets("Data")
NxtRow = Dsht.[DataRange].Rows.Count
With Dsht
..Cells(NxtRow, 2) = PDate 'Date
..Cells(NxtRow, 3) = InProject 'Project
..Cells(NxtRow, 4) = Hours.Text 'Hours
..Cells(NxtRow, 5) = Notes.Text 'Comments
End With
End Sub
I'm trying to get a userform to transfer some data to a sheet (called
Data). I've entered a dynamic range (called DataRange) in Data using
the standard =OFFSET(Data!$B$2,0,0,COUNTA(Data!$B:$B)-1)
All I want to do is to transfer some info from the Form to the next
empty row in Data, without selecting the Data sheet. My problem is that
when the routine first runs, this line of code correctly counts the row
number that I need:
NxtRow = Dsht.[DataRange].Rows.Count + 1
However, on subsequent clicks of the userform enter button, the new
data just overwrites the previous entry. Using a msgbox, I discovered
that the NxtRow variable, and so the DataRange.Rows.Count value, is not
updating. The full code being called by the Private Sub Enter_Click()
procedure is as follows. Thanks ever so for any help,
Graham
Private Sub Transdata()
Dim NxtRow As Long
Dim Dsht As Worksheet
Set Dsht = ActiveWorkbook.Worksheets("Data")
NxtRow = Dsht.[DataRange].Rows.Count
With Dsht
..Cells(NxtRow, 2) = PDate 'Date
..Cells(NxtRow, 3) = InProject 'Project
..Cells(NxtRow, 4) = Hours.Text 'Hours
..Cells(NxtRow, 5) = Notes.Text 'Comments
End With
End Sub