Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Need to replace one dynamic variable with another dynamic variable | Excel Worksheet Functions | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
dynamic summed range based on a variable | Excel Worksheet Functions | |||
Is there a method for refreshing an object variable? | Excel Programming | |||
Problem trying to us a range variable as an array variable | Excel Programming |