Macro to insert formula failing...need help

[Dagonini]

Hi,

I am trying to make a macro to insert a column then do an autofill with
a formula. I have:

Sub calculateage()

Rows("1:1").Select
Selection.Delete Shift:=xlUp
Selection.Insert Shift:=xlDown
Range("H1").Select
ActiveCell.FormulaR1C1 = "=TODAY()"


Range("AU1").Select
ActiveCell.FormulaR1C1 = "=int((TODAY()-H1)/365.25)"
Selection.AutoFill Destination:=Range("AU2:AU20000"),
Type:=xlFillDefault

End Sub

However, this line doesn't work:
Selection.AutoFill Destination:=Range("AU2:AU20000"),
Type:=xlFillDefault

I got it working but when it did work it inserted H1 from the formula
on every line when I need it to run down H1, h2, h3, ect. Any ideas of
what might be going on?

The spreadsheet has people's date of birth in column H. I need to add
a column that will calculate their age. (then it will need to total
out how many people in different age groups but I'm not to that point
yet)

Thank you in advance,
Mary

 

[Niek Otten]

..FormulaR1C1 and H1 conflict with each other.
Use .Formula or use RC[-39] as address

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I am trying to make a macro to insert a column then do an autofill with
| a formula. I have:
|
| Sub calculateage()
|
| Rows("1:1").Select
| Selection.Delete Shift:=xlUp
| Selection.Insert Shift:=xlDown
| Range("H1").Select
| ActiveCell.FormulaR1C1 = "=TODAY()"
|
|
| Range("AU1").Select
| ActiveCell.FormulaR1C1 = "=int((TODAY()-H1)/365.25)"
| Selection.AutoFill Destination:=Range("AU2:AU20000"),
| Type:=xlFillDefault
|
| End Sub
|
| However, this line doesn't work:
| Selection.AutoFill Destination:=Range("AU2:AU20000"),
| Type:=xlFillDefault
|
| I got it working but when it did work it inserted H1 from the formula
| on every line when I need it to run down H1, h2, h3, ect. Any ideas of
| what might be going on?
|
| The spreadsheet has people's date of birth in column H. I need to add
| a column that will calculate their age. (then it will need to total
| out how many people in different age groups but I'm not to that point
| yet)
|
| Thank you in advance,
| Mary
|

 

[ljhc29]

Hi Mary...

why don´t you use the formula "datedif(birth_date; today(); "M" | "Y"
| "D" ) ???

the parameters:
"M" if you want get the result in months
"Y" if you want the result in years
"D" if you want the result in days

I use it like this:

ActiveCell.FormulaR1C1 =
"=DATEDIF(RC[-2],TODAY(),""M"")-(PRODUCT(RC[-1],12))"
Columns("O:O").Select
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O2500"),
Type:=xlFillDefault

Bye!

 

[Bob Phillips]

Range("AU1:AU20000").Formula = "=INT((TODAY()-H1)/365.25)"

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)