find the next empty row
 

I'm using a userform with textboxes to fill in data. Which then gets placed
in the next available row between C10:C19. So far I have the following code
written. Can anyone help and see what changes I need to make to achieve my
goal?

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


'find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _
..End(xlUp).Offset(1, 0).Row + 1


'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
--
ASU

find the next empty row
 

If you want the first available empty row in a worksheet:

Sub LocatEmptyRow()
'
' identify the first empty row in a worksheet
'
For iRow = 1 To 65536
If Application.CountA(Rows(iRow)) = 0 Then
MsgBox (iRow)
Exit Sub
End If
Next
MsgBox ("Worksheet has no empty rows.")
End Sub

Don't be concerned about the 65536 in the For loop. The loop only continues
until the empty row is found.
--
Gary''s Student


"ASU" wrote:

I'm using a userform with textboxes to fill in data. Which then gets placed
in the next available row between C10:C19. So far I have the following code
written. Can anyone help and see what changes I need to make to achieve my
goal?

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


'find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row + 1


'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
--
ASU

find the next empty row
 

Thanks for that. But how do I paste the data to the database and clear the
textboxes after it transfirs. I know the last part of my code is wrong, but I
reckon it only needs a small change.
--
ASU


"Gary''s Student" wrote:

If you want the first available empty row in a worksheet:

Sub LocatEmptyRow()
'
' identify the first empty row in a worksheet
'
For iRow = 1 To 65536
If Application.CountA(Rows(iRow)) = 0 Then
MsgBox (iRow)
Exit Sub
End If
Next
MsgBox ("Worksheet has no empty rows.")
End Sub

Don't be concerned about the 65536 in the For loop. The loop only continues
until the empty row is found.
--
Gary''s Student


"ASU" wrote:

I'm using a userform with textboxes to fill in data. Which then gets placed
in the next available row between C10:C19. So far I have the following code
written. Can anyone help and see what changes I need to make to achieve my
goal?

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


'find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row + 1


'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
--
ASU