Goto a dynamic cell address within a macro
 

My workbook is formula driven and the tables will dynamically expand or
contract with the size of the data. I have a need to move to the next
available row before starting the next subroutine. I have created a formula
that computes the correct cell address but I don't know how to structure a
goto command within the macro to use it.

Can you help?

Goto a dynamic cell address within a macro
 

If you know the top of the data

assume A1

Dim rng as Range
set rng = Range("A1").End(xldown).Offset(1,0)
rng.Select

or if there could be empty cells in column A

Dim rng as Range
set rng = Cells(rows.count,1).End(xlup).Offset(1,0)
rng.select
are possibilities

--
Regards,
Tom Ogilvy

"blkane" wrote in message
...
My workbook is formula driven and the tables will dynamically expand or
contract with the size of the data. I have a need to move to the next
available row before starting the next subroutine. I have created a
formula
that computes the correct cell address but I don't know how to structure a
goto command within the macro to use it.

Can you help?

Goto a dynamic cell address within a macro
 

Thanks Tom,

Unfortunately, going to the top of the range and hitting "end" "pagedown"
doesn't work. I'm copying a named range from another worksheet. The named
range contains formulas where data is visible only if a value exists. When I
copy the range to my worksheet, the cells that appeared blank are messing me
up. When I hit "end", "pagedown", it goes to the row that corresponds to the
end of the named range instead of the end of the visible data.

Hope that's not too confusing.

I created a formula that determines the cell address of where I need to
navigate. This address will always change each time the workbook is used. I
used "Application.Goto Reference:=" to go to the formula. What I need is a
command that will look at the value of the formula to obtain the cell address
and go to it.

Any other thoughts?

"Tom Ogilvy" wrote:

If you know the top of the data

assume A1

Dim rng as Range
set rng = Range("A1").End(xldown).Offset(1,0)
rng.Select

or if there could be empty cells in column A

Dim rng as Range
set rng = Cells(rows.count,1).End(xlup).Offset(1,0)
rng.select
are possibilities

--
Regards,
Tom Ogilvy

"blkane" wrote in message
...
My workbook is formula driven and the tables will dynamically expand or
contract with the size of the data. I have a need to move to the next
available row before starting the next subroutine. I have created a
formula
that computes the correct cell address but I don't know how to structure a
goto command within the macro to use it.

Can you help?

Goto a dynamic cell address within a macro
 

Terms like I create a formula and I need to look at the value of the formula
just can't be dealt with. Its like playing I'm thinking of a number.

where is the formula, what is the formula, what does it produce (an
example).

say the formula is on sheet1 in cell Z36 and it displays a string like
Sheet1!F22

Then you can do

application.Goto Reference:=Range(Worksheets("sheet1").Range("Z36") .Text)


--
Regards,
Tom Ogilvy



"blkane" wrote in message
...
Thanks Tom,

Unfortunately, going to the top of the range and hitting "end" "pagedown"
doesn't work. I'm copying a named range from another worksheet. The
named
range contains formulas where data is visible only if a value exists.
When I
copy the range to my worksheet, the cells that appeared blank are messing
me
up. When I hit "end", "pagedown", it goes to the row that corresponds to
the
end of the named range instead of the end of the visible data.

Hope that's not too confusing.

I created a formula that determines the cell address of where I need to
navigate. This address will always change each time the workbook is used.
I
used "Application.Goto Reference:=" to go to the formula. What I need is
a
command that will look at the value of the formula to obtain the cell
address
and go to it.

Any other thoughts?

"Tom Ogilvy" wrote:

If you know the top of the data

assume A1

Dim rng as Range
set rng = Range("A1").End(xldown).Offset(1,0)
rng.Select

or if there could be empty cells in column A

Dim rng as Range
set rng = Cells(rows.count,1).End(xlup).Offset(1,0)
rng.select
are possibilities

--
Regards,
Tom Ogilvy

"blkane" wrote in message
...
My workbook is formula driven and the tables will dynamically expand
or
contract with the size of the data. I have a need to move to the next
available row before starting the next subroutine. I have created a
formula
that computes the correct cell address but I don't know how to
structure a
goto command within the macro to use it.

Can you help?

Goto a dynamic cell address within a macro
 

Tom,

Thanks. I guess I am asking it to have the "I'm thinking of a number"
intelligence.

I'll have to think of a plan b.

"Tom Ogilvy" wrote:

Terms like I create a formula and I need to look at the value of the formula
just can't be dealt with. Its like playing I'm thinking of a number.

where is the formula, what is the formula, what does it produce (an
example).

say the formula is on sheet1 in cell Z36 and it displays a string like
Sheet1!F22

Then you can do

application.Goto Reference:=Range(Worksheets("sheet1").Range("Z36") .Text)


--
Regards,
Tom Ogilvy



"blkane" wrote in message
...
Thanks Tom,

Unfortunately, going to the top of the range and hitting "end" "pagedown"
doesn't work. I'm copying a named range from another worksheet. The
named
range contains formulas where data is visible only if a value exists.
When I
copy the range to my worksheet, the cells that appeared blank are messing
me
up. When I hit "end", "pagedown", it goes to the row that corresponds to
the
end of the named range instead of the end of the visible data.

Hope that's not too confusing.

I created a formula that determines the cell address of where I need to
navigate. This address will always change each time the workbook is used.
I
used "Application.Goto Reference:=" to go to the formula. What I need is
a
command that will look at the value of the formula to obtain the cell
address
and go to it.

Any other thoughts?

"Tom Ogilvy" wrote:

If you know the top of the data

assume A1

Dim rng as Range
set rng = Range("A1").End(xldown).Offset(1,0)
rng.Select

or if there could be empty cells in column A

Dim rng as Range
set rng = Cells(rows.count,1).End(xlup).Offset(1,0)
rng.select
are possibilities

--
Regards,
Tom Ogilvy

"blkane" wrote in message
...
My workbook is formula driven and the tables will dynamically expand
or
contract with the size of the data. I have a need to move to the next
available row before starting the next subroutine. I have created a
formula
that computes the correct cell address but I don't know how to
structure a
goto command within the macro to use it.

Can you help?