next empty cell in a row

[Guest]

Can anyboby help me!
I'm trying to write a code that finds the next empty row between "C10 to
C19" on "sheet2" using a command button(1) on "sheet1" of a workbook. Then
find the next empty cell in that row and insert the data from 5 textboxes (in
a userform(1)) to the respective cells. And then clear the data in the
textboxes.
What I have so far is this and it's not workin:


Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet2")

'find first empty row in database

For iRow = 10 To 19
If Application.CountA(Rows(iRow)) = 0 Then
MsgBox (iRow)
Exit Sub
End If
Next
MsgBox ("Worksheet has no empty rows.")

'check for a part number

If Trim(Me.TextBox1.Value) = "" Then
Me.TextBox1.SetFocus
MsgBox "Please enter a date"
Exit Sub
End If

'copy the data to the database

ws.Cells(10, 3).Value = Me.TextBox1.Value
ws.Cells(10, 4).Value = Me.TextBox2.Value
ws.Cells(10, 5).Value = Me.TextBox3.Value
ws.Cells(10, 6).Value = Me.TextBox4.Value
ws.Cells(10, 7).Value = Me.TextBox5.Value

'clear the data

Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.TextBox3.Value = ""
Me.TextBox4.Value = ""
Me.TextBox5.Value = ""
Me.TextBox1.SetFocus

End Sub

 

[Bob Phillips]

Try this

Private Sub CommandButton1_Click()
Dim iRow As Long
Dim ws As Worksheet
Dim fOK As Boolean


Set ws = Worksheets("sheet1")

With Me

'find first empty row in database

For iRow = 10 To 19
If Application.CountA(Rows(iRow)) = 0 Then
MsgBox iRow
fOK = True
Exit For
End If
Next
If Not fOK Then
MsgBox ("Worksheet has no empty rows.")
Exit Sub
End If

'check for a part number

If Trim(.TextBox1.Value) = "" Then
With .TextBox1
MsgBox "Please enter a date"
.SetFocus
End With
Else

'copy the data to the database

ws.Cells(10, 3).Value = TextBox1.Value
ws.Cells(10, 4).Value = TextBox2.Value
ws.Cells(10, 5).Value = TextBox3.Value
ws.Cells(10, 6).Value = TextBox4.Value
ws.Cells(10, 7).Value = TextBox5.Value

'clear the data

TextBox1.Value = ""
TextBox2.Value = ""
TextBox3.Value = ""
TextBox4.Value = ""
TextBox5.Value = ""
TextBox1.SetFocus

End If

End With

End Sub



--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

 

[Guest]

Thanks very much for your answer. It works great until it comes to 'copy the
data to the database'. It's pasting the values to the first row (10) only.
How can I get it to paste the values along the selected row.
The problem lies in the section of code where it says:
ws.Cells(10, 3).Value=TextBox1.Value.......Etc.

Many thanks