Offset Method and Interpreting Absolutes in Range Objects
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
|