VBA: Search column A for next cell that's empty, then put data.
Posted by Ron M on January 24, 2002 12:24 PM
Not new to VB but New to VBA. I am using standard worksheet, and I use columns A thru L.
I have created a user form with 12 textboxes and 2 command buttons, Enter and Close. With the user form I can enter the data into each textbox, press " Enter", and it places the data in a row. Easy as pie. The test code that I use is this:
Private Sub cmdEnter_Click()
Range("A1").Select
ActiveCell.FormulaR1C1 = txtName
Range("B1").Select
ActiveCell.FormulaR1C1 = txtNumber
Range("C1").Select
ActiveCell.FormulaR1C1 = txtOldLe
'keeps going on to Range("L1")
End Sub
What I need to do next is find the next cell in column "A?", that is empty, to enter new data using the form. I know there is a way, and when I see it I'm going to be like, (Duh!). Just can't seem to think. Thanks Ron M.
Posted by Barrie Davidson on January 24, 2002 12:55 PM
You can try something like this:
Private Sub cmdEnter_Click()
Range("A1").End(xlDown).Offset(1, 0).FormulaR1C1 = txtName
Range("A1").End(xlDown).Offset(1, 1).FormulaR1C1 = txtNumber 'this is column B
Range("A1").End(xlDown).Offset(1, 2).FormulaR1C1 = txtOldLe ' this is column C
'keeps going on to Range("L1")
End Sub
Regards,
BarrieBarrie Davidson
Posted by Jacob on January 24, 2002 12:56 PM
Hi
This is what I use:
range("A65536").end(xlup).offset(1,0).select
This will be the first blank row
HTH
Jacob
Posted by Richard Winfield on January 24, 2002 12:57 PM
This should do it:
Sub TryThis()
'Copy to first blank row
Range("A1", Range("A1").End(xlDown)).Copy
End Sub
Rick
Posted by Ron M on January 24, 2002 1:08 PM
I did not get the (Duh!) like I thought. But it works for me. Thanks a lot.
Ron M
