Change cell reference type

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ladymuck - 24 Sep 2007 15:32 GMT

I have the following line in my macro code:
KeyCell = ActiveCell.Address
This is used in the line:
Range("D2").Formula = "=IF('Original Data'!" & KeyCell &
"<>0,'Original Data'!" & KeyCell & ",TODAY())"

It evaluates fine but, further down in my code, I copy this formula down a
large number of rows. Unfortunately, KeyCell is an absolute cell reference
and I want it to be relative. What do I need to do to change this?

Many thanks for your help

Reply to this Message

Bob Phillips - 24 Sep 2007 15:45 GMT

KeyCell = ActiveCell.Address(0,0)

Signature

---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

>I have the following line in my macro code:
> KeyCell = ActiveCell.Address
[quoted text clipped - 7 lines]
>
> Many thanks for your help

Reply to this Message

Charles Chickering - 24 Sep 2007 15:48 GMT

KeyCell = ActiveCell.Address(False, False)
the first optional variable is row absolute the second is column. This makes
both relative

Signature

Charles Chickering

"A good example is twice the value of good advice."

> I have the following line in my macro code:
> KeyCell = ActiveCell.Address
[quoted text clipped - 7 lines]
>
> Many thanks for your help

Reply to this Message

Keithlo - 24 Sep 2007 15:48 GMT

I think you need to make KeyCell = ActiveCell.Address(RowAbsolute:=False,
ColumnAbsolute:=False)

This way the dollar signs are not returned, and when you go to copy it wil
advance the references.

Hope this helps,

Keith

> I have the following line in my macro code:
> KeyCell = ActiveCell.Address
[quoted text clipped - 7 lines]
>
> Many thanks for your help

Reply to this Message

PatrickS - 24 Sep 2007 15:56 GMT

Why are you using activecell to begin with? Wouldn't an absolute address be
better here?

Reply to this Message

Ladymuck - 24 Sep 2007 16:08 GMT

Thanks for your suggestions, they work brilliantly.

Reply to this Message