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

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


--
Kind regards,

Niek Otten
Microsoft MVP - Excel


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

Or use ActiveCell.Formula instead of ActiveCell.FormulaR1C1

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


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

Many thanks

I'm still getting used to applying RD references

"Niek Otten" wrote:

Or use ActiveCell.Formula instead of ActiveCell.FormulaR1C1

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


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