What am I doing wrong?
 

I have a Sheet(form) that I am trying to Populate (row by
row) using a UserForm. The problem is that the area to be
populated is in the middle of the sheet (i.e A21:AD52).

I have created range names for the columns and even
created a range name to define the range (A21:AD52), but
alas, my code is not working. Please help

Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) + 1

'Transfer the data to the sheet
Range("Release_PN") = TextPN.Text
Range("Release_Description") = TextDescription.Text
If CheckAccessory.Value = True Then Range("D21") = "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub

What am I doing wrong?
 

Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) ' remove +1 since offset is zero based

'Transfer the data to the sheet
Range("Release_PN")(1).Offset(NextRow,0) = TextPN.Text
Range("Release_Description")(1).Offset(NextRow,0) = TextDescription.Text
If CheckAccessory.Value = True Then Range("D21") = "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub

--
Regards,
Tom Ogilvy

"John Petty" wrote in message
...
I have a Sheet(form) that I am trying to Populate (row by
row) using a UserForm. The problem is that the area to be
populated is in the middle of the sheet (i.e A21:AD52).

I have created range names for the columns and even
created a range name to define the range (A21:AD52), but
alas, my code is not working. Please help

Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) + 1

'Transfer the data to the sheet
Range("Release_PN") = TextPN.Text
Range("Release_Description") = TextDescription.Text
If CheckAccessory.Value = True Then Range("D21") = "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub

What am I doing wrong?
 

Tom,

Thanks for the help. It definitely loads the data into
the cells, but it is not dropping done to the next row
after loading in fresh data to the form. It just
overwrites the existing data. Any ideas?
-----Original Message-----
Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) ' remove +1 since offset is zero based

'Transfer the data to the sheet
Range("Release_PN")(1).Offset(NextRow,0) = TextPN.Text
Range("Release_Description")(1).Offset(NextRow,0) =
TextDescription.Text
If CheckAccessory.Value = True Then Range("D21") = "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub

--
Regards,
Tom Ogilvy

"John Petty" wrote in message
...
I have a Sheet(form) that I am trying to Populate (row
by
row) using a UserForm. The problem is that the area to
be
populated is in the middle of the sheet (i.e A21:AD52).

I have created range names for the columns and even
created a range name to define the range (A21:AD52), but
alas, my code is not working. Please help

Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) + 1

'Transfer the data to the sheet
Range("Release_PN") = TextPN.Text
Range("Release_Description") = TextDescription.Text
If CheckAccessory.Value = True Then Range("D21")
= "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub


.

What am I doing wrong?
 

Thanks again Tom. My mistake, I forgot to tag the Range
("ReleaseAction") in my data transfer so it always saw
that field as open.

Thanks again.
-----Original Message-----
Tom,

Thanks for the help. It definitely loads the data into
the cells, but it is not dropping done to the next row
after loading in fresh data to the form. It just
overwrites the existing data. Any ideas?
-----Original Message-----
Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) ' remove +1 since offset is zero
based

'Transfer the data to the sheet
Range("Release_PN")(1).Offset(NextRow,0) =
TextPN.Text
Range("Release_Description")(1).Offset(NextRow,0) =
TextDescription.Text
If CheckAccessory.Value = True Then Range("D21")
= "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub

--
Regards,
Tom Ogilvy

"John Petty" wrote in message
...
I have a Sheet(form) that I am trying to Populate (row
by
row) using a UserForm. The problem is that the area
to
be
populated is in the middle of the sheet (i.e A21:AD52).

I have created range names for the columns and even
created a range name to define the range (A21:AD52),
but
alas, my code is not working. Please help

Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) + 1

'Transfer the data to the sheet
Range("Release_PN") = TextPN.Text
Range("Release_Description") = TextDescription.Text
If CheckAccessory.Value = True Then Range("D21")
= "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub


.

.

What am I doing wrong?
 

Your basing your nextRow value on the range ReleaseAction - but your code
doesn't show you writing anything to that range. It appears your post
didn't include all your code, but it should work if you are writing a value
each time in ReleaseAction. You could change the alogrithm to

cnt = Range("Release_PN").count
nextRow = Range("Release_PN").Offset(cnt,0).Resize(1).End(xl up).Row
if nextRow < Range("Release_PN")(1).Row then
nextRow = 0
else
nextRow = NextRow - Range("Release_PN")(0).row
End if

Or use

Application.WorksheetFunction.CountA(Range _
("Release_PN"))

--
Regards,
Tom Ogilvy


"John Petty" wrote in message
...
Tom,

Thanks for the help. It definitely loads the data into
the cells, but it is not dropping done to the next row
after loading in fresh data to the form. It just
overwrites the existing data. Any ideas?
-----Original Message-----
Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) ' remove +1 since offset is zero based

'Transfer the data to the sheet
Range("Release_PN")(1).Offset(NextRow,0) = TextPN.Text
Range("Release_Description")(1).Offset(NextRow,0) =
TextDescription.Text
If CheckAccessory.Value = True Then Range("D21") = "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub

--
Regards,
Tom Ogilvy

"John Petty" wrote in message
...
I have a Sheet(form) that I am trying to Populate (row
by
row) using a UserForm. The problem is that the area to
be
populated is in the middle of the sheet (i.e A21:AD52).

I have created range names for the columns and even
created a range name to define the range (A21:AD52), but
alas, my code is not working. Please help

Private Sub CommandNext_Click()
Dim NextRow As Integer
On Error Resume Next

'Make sure that the Release form is Active
Sheets("Release").Activate

' Determine the next Empty Row
NextRow = _
Application.WorksheetFunction.CountA(Range _
("ReleaseAction")) + 1

'Transfer the data to the sheet
Range("Release_PN") = TextPN.Text
Range("Release_Description") = TextDescription.Text
If CheckAccessory.Value = True Then Range("D21")
= "X"
(and the rest of the data)

' Reset the Userform for the next row
TextPN.Text = ""
TextDescription.Text = ""
CheckAccessory.Value = False
End Sub


.