Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a formula in a cell that calculates a given date. I want to write a VBA macro that will take the formula, convert it to the resulting date value and copy that value to another/adjacent cell.

I've attempted to record the macro using "F2" and "F9" however the VBA script returns, e.g.

ActiveCell.FormulaR1C1 = "12/31/2009". 

I want to write the macro to work in future years where the formula for the date cell could be 12/31/2012, 2013, etc. I have built formulas to eliminate effect of leap year, so I can't just add 365 to derive next year.

share|improve this question

This will copy the value and format from the current active cell to the cell to the right

Sub CopyValue()
    ActiveCell.Offset(0, 1).Value = ActiveCell.Value
    ActiveCell.Offset(0, 1).NumberFormat = ActiveCell.NumberFormat
End Sub
share|improve this answer
Thanks, Chris. That was perfect for what I needed. – Chuck May 15 '12 at 13:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .