Change cell reference type
 

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

Change cell reference type
 

KeyCell = ActiveCell.Address(0,0)

--
---
HTH

Bob

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



"Ladymuck" wrote in message
...
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

Change cell reference type
 

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

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


"Ladymuck" wrote:

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

Change cell reference type
 

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

"Ladymuck" wrote:

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

Change cell reference type
 

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

Change cell reference type
 

Thanks for your suggestions, they work brilliantly.