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

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

What I am doing is trying to mimic the effects of copying a cell to the right
and watch its address update for relative references. I fitured the easiest
way to do this was to set a range variable to the a range with the address.
Then I could take advantage of the Offset method do that the columns would
update properly. Problem is that the variable holding the address is
interpreted as an absolute cell address - which its not. Second problem is
that Offset Method is not smart enough ( based on how I am using it) to know
whether or not to udpate address. As I do not know ahead of time whether the
cell address will be absolute or not, I apply the Offset Method to it
blindly. Effectively I want to update an address pretending its copied to
the right and I also want to take into account any absolute values that may
be inherent in that address.

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

Thanks

EM

"JNW" wrote:

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

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

What I would do then is use both the offset and fill statements.

Replace:
Set r = Range(Suspectedrng)
RevisedRngRight = r.Offset(0, 1).Address
With:
Range(Range(Suspectedrng), Range(Suspectedrng).Offset(0, 1)).FillRight

This has the same effect as hovering over the small square in the bottom
right of a selected range and dragging the range.

Hope that is what you are looking for.

"ExcelMonkey" wrote:

What I am doing is trying to mimic the effects of copying a cell to the right
and watch its address update for relative references. I fitured the easiest
way to do this was to set a range variable to the a range with the address.
Then I could take advantage of the Offset method do that the columns would
update properly. Problem is that the variable holding the address is
interpreted as an absolute cell address - which its not. Second problem is
that Offset Method is not smart enough ( based on how I am using it) to know
whether or not to udpate address. As I do not know ahead of time whether the
cell address will be absolute or not, I apply the Offset Method to it
blindly. Effectively I want to update an address pretending its copied to
the right and I also want to take into account any absolute values that may
be inherent in that address.

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

Thanks

EM

"JNW" wrote:

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

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

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

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