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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
> >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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?
> > >
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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?
> > > >
> > >
> > >
> > >
>
>
>