CODEWITHSUNDEEP
excelvba
Novice
Novice

Reputation: 393

VBA formula to calculate Age from Born Date

I have Born Dates and want apply this formula

=(YEAR(NOW())-YEAR(A2))

in VBA for calculate age for whole row of dates

for example

      A           B
1  BornDate      Age
2  09.06.1991    28
3  02.07.1973
4
5

my code works only for first two and stop without any error.

Sub btn_GetAge()

    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Sheet1")
    LastRow = .Cells(.Rows.Count).End(xlUp).Row
    .Range("B2:B" & LastRow) = "=(YEAR(NOW())-YEAR(A1))"
    End With

End Sub

Any idea or choose different formula ?

Upvotes: 1

Views: 8729

Answers (3)

Error 1004
Error 1004

Reputation: 8220

Try:

Option Explicit

Sub Test()

    Dim Lastrow As Long, i As Long
    Dim CurrenctYear As Long, LoopYear As Long

    With ThisWorkbook.Worksheets("Sheet1")

        Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            CurrenctYear = Year(Now)

        For i = 1 To Lastrow
            LoopYear = Year(.Range("A" & i).Value)
            .Range("A" & i).Offset(0, 1).Value = CurrenctYear - LoopYear
        Next i

    End With

End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96753

You were very close:

Sub btn_GetAge()

    Dim LastRow As Long

    With ThisWorkbook.Worksheets("Sheet1")
        LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
        .Range("B2:B" & LastRow) = "=YEAR(TODAY())-YEAR(A2)"
    End With

End Sub

enter image description here

Upvotes: 3

SJR
SJR

Reputation: 23081

1) Cells requires a row and column, e.g. A1 is Cells(1,1)

2) Your formula (and better to specify the property) starts in row 2 but refers to A1

Sub btn_GetAge()

Dim LastRow As Long

With ThisWorkbook.Worksheets("Sheet1")
    LastRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    .Range("B2:B" & LastRow).Formula = "=(YEAR(NOW())-YEAR(A2))"
End With

End Sub

Upvotes: 4

Related Questions