Applying a formula to a cell

[Guest]

I would like to set up a button in a spreadsheet that adds 2 years to the
value of of the currently selected cell containing a date. the forumla would
look something like this: =DATE(YEAR(A1)+2,MONTH(A2),DAY(A3)) but is there a
way I can have this formula applied to a cell when I press a button? I would
like the cell to contain the result, rather than the formula.
Thanks guys.

 

[Brian Withun]

I would like to set up a button in a spreadsheet that adds 2 years to the
value of of the currently selected cell containing a date. the forumla would
look something like this: =DATE(YEAR(A1)+2,MONTH(A2),DAY(A3)) but is there a
way I can have this formula applied to a cell when I press a button? I would
like the cell to contain the result, rather than the formula.
Thanks guys.

try this event for your button:

Private Sub AddTwoYears_Click()

Dim CurrentValue As Date
On Error GoTo CannotConvertToDate
Let CurrentValue = Selection.Value
On Error GoTo 0
Selection.Value = DateSerial(Year(CurrentValue) + 2,
Month(CurrentValue), Day(CurrentValue))
Exit Sub

CannotConvertToDate:
MsgBox "Selection is not a date"

End Sub

Brian Herbert Withun

 

[Guest]

Thanks Brian, that was exactly what I was looking for. Makes me realize I
need to learn VB!