Refreshing a dynamic range variable
 

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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Refreshing a dynamic range variable
 

The problem is that your code needs to read:

NxtRow = Dsht.[DataRange].Rows.Count + 2

The reason? Your dynamic range starts at B2 and its row count is one
less than the actual number of entries in the column because the OFFSET
formula uses COUNTA(Data!$B:$B)-1. I assume you have field names in row
1. So if your dynamic range is B2:B4,

Dsht.[DataRange].Rows.Count = 3

You were adding 1 to this to get a value of 4, so your new data was
going into row 4, which is already occupied. If you add 2 instead of 1,
you'll get row 5, which would be correct.

--
HTH,
Dianne

In ,
Gromit typed:
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


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Refreshing a dynamic range variable
 

Aha,

Thanks very much for thinking more clearly than I could, Dianne,

Much appreciated,

Graha

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

Refreshing a dynamic range variable
 

You're welcome! I've gotten plenty out of this newsgroup -- I'm happy to
contribute when I can.

Dianne

In ,
Gromit typed:
Aha,

Thanks very much for thinking more clearly than I could, Dianne,

Much appreciated,

Graham


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements