software

Hi all,

I am trying to adapt the information from
www.contextures.com/xlUserForm01.html to make a userform for my
worksheet. I got everything working, almost!!! The form enters data in A2
of my sheet and I can't figure out how to make it enter it into the next
available cell in the range of F17:F50. There were alot of places I had to
change things in Debra's code to work for my form and I think I must have
done something wrong. Any help would be appreciated.
Here is the code that I have so far:

Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets(quot;DataInputquot;)

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'check for a employee name
If Trim(Me.txtName.Value) = quot;quot; Then
Me.txtName.SetFocus
MsgBox quot;Please enter New Employee Namequot;
Exit Sub
End If

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value

'clear the data
Me.txtName.Value = quot;quot;

End Sub

Thanks
Mike Rogers

This bit of code

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

finds the last used row in column A (1), so c hange that to

'find first empty row in database
iRow = ws.Cells(Rows.Count, quot;Fquot;) _
.End(xlUp).Offset(1, 0).Row

and this bit copies it

'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value

so change that to

'copy the data to the database
ws.Cells(iRow, quot;Fquot;).Value = Me.txtName.ValueBefore the copy though, you need to increment iRow by one, as you have found
the last used cell, not the first free one.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Mike Rogersquot; gt; wrote in message
...
gt; Hi all,
gt;
gt; I am trying to adapt the information from
gt; www.contextures.com/xlUserForm01.html to make a userform for my
gt; worksheet. I got everything working, almost!!! The form enters data in
A2
gt; of my sheet and I can't figure out how to make it enter it into the next
gt; available cell in the range of F17:F50. There were alot of places I had
to
gt; change things in Debra's code to work for my form and I think I must have
gt; done something wrong. Any help would be appreciated.
gt; Here is the code that I have so far:
gt;
gt; Private Sub cmdAdd_Click()
gt; Dim iRow As Long
gt; Dim ws As Worksheet
gt; Set ws = Worksheets(quot;DataInputquot;)
gt;
gt; 'find first empty row in database
gt; iRow = ws.Cells(Rows.Count, 1) _
gt; .End(xlUp).Offset(1, 0).Row
gt;
gt; 'check for a employee name
gt; If Trim(Me.txtName.Value) = quot;quot; Then
gt; Me.txtName.SetFocus
gt; MsgBox quot;Please enter New Employee Namequot;
gt; Exit Sub
gt; End If
gt;
gt; 'copy the data to the database
gt; ws.Cells(iRow, 1).Value = Me.txtName.Value
gt;
gt; 'clear the data
gt; Me.txtName.Value = quot;quot;
gt;
gt; End Sub
gt;
gt; Thanks
gt; Mike Rogers
Bob,
Thanks for the help!! I can see now how it finds column F and the next
empty row. I made those two changes and it worked. I did not try to
increment iRow by one because I don't really know how or where to do that.
But bottom line...It works!!!
Thanks again for your help.

quot;Bob Phillipsquot; wrote:

gt; This bit of code
gt;
gt; 'find first empty row in database
gt; iRow = ws.Cells(Rows.Count, 1) _
gt; .End(xlUp).Offset(1, 0).Row
gt;
gt; finds the last used row in column A (1), so c hange that to
gt;
gt; 'find first empty row in database
gt; iRow = ws.Cells(Rows.Count, quot;Fquot;) _
gt; .End(xlUp).Offset(1, 0).Row
gt;
gt; and this bit copies it
gt;
gt; 'copy the data to the database
gt; ws.Cells(iRow, 1).Value = Me.txtName.Value
gt;
gt; so change that to
gt;
gt; 'copy the data to the database
gt; ws.Cells(iRow, quot;Fquot;).Value = Me.txtName.Value
gt;
gt;
gt; Before the copy though, you need to increment iRow by one, as you have found
gt; the last used cell, not the first free one.
gt;
gt; --
gt; HTH
gt;
gt; Bob Phillips
gt;
gt; (remove nothere from email address if mailing direct)
gt;
gt; quot;Mike Rogersquot; gt; wrote in message
gt; ...
gt; gt; Hi all,
gt; gt;
gt; gt; I am trying to adapt the information from
gt; gt; www.contextures.com/xlUserForm01.html to make a userform for my
gt; gt; worksheet. I got everything working, almost!!! The form enters data in
gt; A2
gt; gt; of my sheet and I can't figure out how to make it enter it into the next
gt; gt; available cell in the range of F17:F50. There were alot of places I had
gt; to
gt; gt; change things in Debra's code to work for my form and I think I must have
gt; gt; done something wrong. Any help would be appreciated.
gt; gt; Here is the code that I have so far:
gt; gt;
gt; gt; Private Sub cmdAdd_Click()
gt; gt; Dim iRow As Long
gt; gt; Dim ws As Worksheet
gt; gt; Set ws = Worksheets(quot;DataInputquot;)
gt; gt;
gt; gt; 'find first empty row in database
gt; gt; iRow = ws.Cells(Rows.Count, 1) _
gt; gt; .End(xlUp).Offset(1, 0).Row
gt; gt;
gt; gt; 'check for a employee name
gt; gt; If Trim(Me.txtName.Value) = quot;quot; Then
gt; gt; Me.txtName.SetFocus
gt; gt; MsgBox quot;Please enter New Employee Namequot;
gt; gt; Exit Sub
gt; gt; End If
gt; gt;
gt; gt; 'copy the data to the database
gt; gt; ws.Cells(iRow, 1).Value = Me.txtName.Value
gt; gt;
gt; gt; 'clear the data
gt; gt; Me.txtName.Value = quot;quot;
gt; gt;
gt; gt; End Sub
gt; gt;
gt; gt; Thanks
gt; gt; Mike Rogers
gt;
gt;
gt;