Offset is for referencing a cell - I am afraid I can't think of anything
that will do what you want unless you write the intelligence behind it
yourself. (you parse out the formula and apply the rules yourself to
transform the formula)

A kludge would be to do the copy and paste on a hidden page or a dummy page
that isn't active and examine the formula.

The other think you could look at is Application.ConvertFormula. It has a
last argument of "relative to". However, I believe that is just used for
converting a relative reference to an absolute reference if so requested.
Maybe with some creative manipulation, you can get it to help you achieve
your ends but nothing jumps out at me at the moment.

--
Regards,
Tom Ogilvy


"ExcelMonkey" wrote in message
...
So Tom, what the Monkey WANTS to happen is what would normally happen to
cell
formulas if I was actually copying the cell range that the formula is in
to
the right in Excel:

1) copy a cell with a formula = $A7 to the right, formula in cell to right
=
$A7 or
2) copy a cell with a formula = A7 to the right, formula in cell to right
=
B7 or
3) copy a cell with a formula = A$7 to the right, formula in cell to right
= B$7

The key here being that I am only interested in seeing how the formula
updates after the simulated copy. I thought I could use a range object
AND
the Offset Method to ensure column letter updates. If not, what is the
easiest way for me to simulate this action in code so that I can see a
revised address which ahderes to the absolute constraints of the address.
I
know that actually copying across is one of the best ways to get this
information. But I am specifically trying NOT to do this.

I am not concerned with the address of the cell that the formula is in.
Just the formula itslef. Has my use of the Offset method contradicted
this
goal?

Thanx

EM


"Tom Ogilvy" wrote:

Depends on what you ask for.
? activeCell.Address(0,0)
C7
? activecell.Address(0,0,xlA1,true)
[Book1]Sheet1!C7


However, VBA pays no attention to absolute and relative notation in
working
with arguments to ranges.

It is unclear what the Monkey expects to happen.

Perhaps this:

Suspectedrng= "$A7"
Set r = Range(Suspectedrng)
RevisedRngRight = r.Offset(0, 1).Address(0,1)
? revisedrngright
$B7

--
Regards,
Tom Ogilvy



"JNW" wrote in message
...
To my knowledge, the address command always returns absolute ranges.
You
could test this by removing the $ before the A and trying again.

Depending on what you are doing a fill command may work better. Would
need
more information though.

JNW

"ExcelMonkey" wrote:

I have the following code below which offsets a cell range. Two
questions:

1) Why is the row (7) absoluted in the revised range?
2) The Offset does not appear to recognize the fact that the cell
$A7
has
its column absoluted therefore it increments this to column "B".
Why is
this
(aside from the fact that I told it to do it (0,1)? Is there a way
of
using
the Offset Method to have it interpret absolutes properly?

Thanks


Suspectedrng= "$A7"
Set r = Range(Suspectedrng)
RevisedRngRight = r.Offset(0, 1).Address

?RevisedRngRight
$B$7