I'm new to VBA with excel, so be gentle. I'm having trouble writing a loop to go down a column and find the first empty cell and then fill it with a value from my form. I think I have the logic down, but I keep getting errors so I thought an example would be helpful. Thanks

Dim rng as Range
set rng = Cells(1,1)
do while not isempty(rng)
set rng = rng.offset(1,0)
Loop

would loop down the row as you described.

if the data is contiguous you could do

Dim rng
set rng = cells(rows.count,1).End(xlup)
if not isempty(rng) then set rng = rng.offset(1,0)

--
Regards,
Tom Ogilvy





"Rick" wrote in message
...
I'm new to VBA with excel, so be gentle. I'm having trouble writing a
loop to go down a column and find the first empty cell and then fill it with
a value from my form. I think I have the logic down, but I keep getting
errors so I thought an example would be helpful. Thanks

Rick

Not sure if you mean "first" blank row or "first" blank row at bottom? Could
be different.

To copy B1 to first empty Cell at bottom of column A use this.

Sub Copyit()
Range("B1").Copy Destination:= _
ActiveSheet.Cells(Rows.Count, 1).End(xlUp) _
.Offset(1, 0)
End Sub

To copy B1o first blank Cell in Column A use this.

Sub Copyit22()
Range("B1").Copy Destination:= _
ActiveSheet.Cells.End(xlDown) _
.Offset(1, 0)
End Sub

Gord Dibben Excel MVP

On Sun, 2 May 2004 11:01:05 -0700, "Rick"
wrote:

I'm new to VBA with excel, so be gentle. I'm having trouble writing a loop to go down a column and find the first empty cell and then fill it with a value from my form. I think I have the logic down, but I keep getting errors so I thought an example would be helpful. Thanks