What am I doing wrong?

[John Petty]

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

 

[Tom Ogilvy]

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

 

[John Petty]

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?

 

[john petty]

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.

 

[Tom Ogilvy]

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(xlup).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"))