Selecting the next empty cell

[mm1861]

Greetings,
I'm writing a macro where I need to find and populate a
cell with a formula.

The problem is that the number of rows varies so I need to
find the last row then get the next empty cell. I've been
using: Range("F12").Select
Selection.End(xlDown).Select
to get the last cell in the column, but need the next cell
in the same column. Usually I record a macro and then cut
and paste the code into my larger macro. With this
approach the macro recorder provides absolute addresses
which defeats my ability to use relative address.

I know this is simple, but I haven't been able to find
anything in the help.

Bottom line.. all I need is a way to select R[1]C[0] from
where I am.

Thanks,
Mike

 

[Dave Peterson]

How about:

dim myCell as range
set mycell = range("f12").end(xldown).offset(1,0)

I like to come up from the bottom sometimes, too.

dim mycell as range
with activesheet
set mycell = .cells(.rows.count,"F").end(xlup).offset(1,0)
end with

Greetings,
I'm writing a macro where I need to find and populate a
cell with a formula.

The problem is that the number of rows varies so I need to
find the last row then get the next empty cell. I've been
using: Range("F12").Select
Selection.End(xlDown).Select
to get the last cell in the column, but need the next cell
in the same column. Usually I record a macro and then cut
and paste the code into my larger macro. With this
approach the macro recorder provides absolute addresses
which defeats my ability to use relative address.

I know this is simple, but I haven't been able to find
anything in the help.

Bottom line.. all I need is a way to select R[1]C[0] from
where I am.

Thanks,
Mike

 

[mm1861]

Thanks Dave, I'll give that a try

-----Original Message-----
How about:

dim myCell as range
set mycell = range("f12").end(xldown).offset(1,0)

I like to come up from the bottom sometimes, too.

dim mycell as range
with activesheet
set mycell = .cells(.rows.count,"F").end (xlup).offset(1,0)
end with

Greetings,
I'm writing a macro where I need to find and populate a
cell with a formula.

The problem is that the number of rows varies so I need to
find the last row then get the next empty cell. I've been
using: Range("F12").Select
Selection.End(xlDown).Select
to get the last cell in the column, but need the next cell
in the same column. Usually I record a macro and then cut
and paste the code into my larger macro. With this
approach the macro recorder provides absolute addresses
which defeats my ability to use relative address.

I know this is simple, but I haven't been able to find
anything in the help.

Bottom line.. all I need is a way to select R[1]C[0] from
where I am.

Thanks,
Mike

--

Dave Peterson
(e-mail address removed)
.