Do Loop Help

[PRodgers]

Do Loop Help
I am trying to create a macro to loop through a column (column A) with a persons dob and output their age in (column B). I have a basic formula that already calculate the age:

=DATEDIF(Y2,TODAY(),"y").

I have got an example of a loop (calulates average) to do this but im unsure of how to add my formula to it:


Sub calculateAge()
Do ActiveCell.FormulaR1C1 = "=Average(RC[-1],RC[-2])"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub




Can anyone help?

[MaximS]

try that:


Sub calculateAge()

Dim Lr As Long

Lr = Range("A" & Rows.Count).End(xlUp).Row

Cells(1, 2).Resize(Lr, 1) = "=DATEDIF(A1,TODAY(),""y"")"

End Sub

[PRodgers]

try that:


Sub calculateAge()

Dim Lr As Long

Lr = Range("A" & Rows.Count).End(xlUp).Row

Range("B2").Resize(Lr - 1,) = "=DATEDIF(Y2,TODAY(),""y"")"

End Sub


Hi thats for the reply, im getting a syntax error on the line

Range("B2").Resize(Lr - 1,) = "=DATEDIF(Y2,TODAY(),""y"")"

[MaximS]

I have edited that and is working now

[PRodgers]

I have edited that and is working now

Thank you so much, that works great :thumb

[PRodgers]

I have tried to write a macro to calculate the Month and Year ie

=TEXT(I2,"mmm-yy")

I have the following code but it isnt working:

Sub calculateMonth()

Dim Lr As Long

Lr = Range("C" & Rows.Count).End(xlUp).Row

Cells(1, 2).Resize(Lr, 1) = "=TEXT(I2,"mmm-yy")"

End Sub

[MaximS]

try that:


Sub calculateAge()

Dim Lr As Long

Lr = Range("A" & Rows.Count).End(xlUp).Row

'Column B for full years
Cells(1, 2).Resize(Lr, 1) = "=DATEDIF(A1,TODAY(),""y"")"
'Column C for full months
Cells(1, 3).Resize(Lr, 1) = "=DATEDIF(A1,TODAY(),""m"")" _
& "-(DATEDIF(A1,TODAY(),""y"")*12)"
'Column D for years and full months in one cell
Cells(1, 4).Resize(Lr, 1) = "=DATEDIF(A1,TODAY(),""y"")&"" yrs. ""&" _
& "IF(DATEDIF(A1,TODAY(),""m"")" _
& "-(DATEDIF(A1,TODAY(),""y"")*12)>0," _
& "DATEDIF(A1,TODAY(),""m"")" _
& "-(DATEDIF(A1,TODAY(),""y"")*12)&"" mths."", """")"
End Sub