VBA - Calculate Age of entire column

[blackgolf]

I need the age in years in Column T from data in Column K.

DOB (Col K) Age (Col T)
12 Jan 1978 ?
23 Mar 1966 ?
5 May 2001 ?

So the VBA macro must look up the DOB for each row of Column K and place the answer in Column T as age in years.
So the result must be:

DOB (Col K) Age (Col T)
12 Jan 1978 45
23 Mar 1966 56
5 May 2001 21

Many thanks

 

[hajiali]

Have not tested but try

Sub calAge()
for each cell in range(“K:K”) ‘ change range to actual range
Cell.offset(0,9)=format(now()-cell,”YYYY”)
Next
End sub

 

[blackgolf]

@hajiali Hi, I ran your code above but get an error on line 1 - for each .......
Compile Error and Syntax error, pointing at the : left and right of the K of range("K:K")
Thank you

 

[xlhelp15]

I need the age in years in Column T from data in Column K.

DOB (Col K) Age (Col T)
12 Jan 1978 ?
23 Mar 1966 ?
5 May 2001 ?

So the VBA macro must look up the DOB for each row of Column K and place the answer in Column T as age in years.
So the result must be:

DOB (Col K) Age (Col T)
12 Jan 1978 45
23 Mar 1966 56
5 May 2001 21

Many thanks

Hi Welcome to MrExcel - Is this the same format you're going to use like 12 Jan 1978 or is there any specific format that your sheet has ?

 

[Fluff]

Please do not mark a post as the solution when it doesn't have one, especially when you are still looking for help.

 

[xlhelp15]

@hajiali Hi, I ran your code above but get an error on line 1 - for each .......
Compile Error and Syntax error, pointing at the : left and right of the K of range("K:K")
Thank you

How about this,

Sub calAge()
Dim LRow As Long
Dim Inc As Integer
LRow = Cells(Rows.Count, "K").End(xlUp).Row
For Inc = 2 To LRow
Cells(Inc, "T").Value = Format(Date - Cells(Inc, "K"), "YY")
Next
End Sub

 

[blackgolf]

Hi @hajiali
I ran the code but get funny results like a long 16 and more digit number (104828736689815)
Different cell formats does not help
Thanks

 

[blackgolf]

Thank you very much xlhelp15, works perfectly.

 

[xlhelp15]

Thanks for your feedback, Glad to hear that!

 

[smozgur]

Thank you very much xlhelp15, works perfectly.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.