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