Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Leaving an empty cell empty | Excel Discussion (Misc queries) | |||
why a reference to an empty cell is not considered empty | Excel Discussion (Misc queries) | |||
Find an empty cell and put a dash in it? | Excel Worksheet Functions | |||
Using the Find tool in EXCEL | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |