Circular Reference Problem

[Graham]

Hi

I'm having trouble with a circular reference.

The aim is to convert a 2 digit number, from the cell to the left, to a
nominal date and return an approximate age.

The following code should apply the formula and copy down to the last row


Range("S2").Select
ActiveCell.FormulaR1C1 =
"=ROUNDDOWN((TODAY()-(IF(R2<10,CONCATENATE(""01/01/"",""200"",R2),CONCATENATE(""01/01/"",""19"",R2))*1))/365,0)"
Selection.AutoFill Destination:=Range("S2:S" & Cells(Rows.Count,
"A").End(xlUp).Row), Type:=xlFillDefault


Once the macro has run the formula is displayed in the cells as

=ROUNDDOWN((TODAY()-(IF($2:$2<10,CONCATENATE("01/01/","200",$2:$2),CONCATENATE("01/01/","19",$2:$2))*1))/365,0)


Any help would be appreciated

 

[Niek Otten]

Maybe you mean

ActiveCell.FormulaR1C1 =
"=ROUNDDOWN((TODAY()-(IF(RC[-1]<10,CONCATENATE(""01/01/"",""200"",RC[-1]),CONCATENATE(""01/01/"",""19"",RC[-1]))*1))/365,0)"
Selection.AutoFill Destination:=Range("S2:S" & Cells(Rows.Count,
"A").End(xlUp).Row), Type:=xlFillDefault

 

[Niek Otten]

Or use ActiveCell.Formula instead of ActiveCell.FormulaR1C1

Note that in R1C1 style R2 is not cell R2, bit Row 2.

 

[Graham]

Many thanks

I'm still getting used to applying RD references