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?
|