If you refer to my other post you can use offset to select the start of your
range (the cell you want to copy) and the end (including all cells you want
to copy to) and use the fill commands (fillright, filldown, fillleft,
fillup). These will copy your formula in the direction you specify.

"ExcelMonkey" wrote:

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