Sub yearformula()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
Cells(i, "b") = Year(Cells(i, "a"))
Next i
End Sub

Better to just leave the value but if you really want the formula instead
Sub yearformula()
For i = 1 To Cells(Rows.Count, "a").End(xlUp).Row
Cells(i, "b").Formula = "=Year(a" & i & ")"
Next i
End Sub
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Scott" wrote in message
...
I have date values going down column 1 as shown in "Example 1". I'm trying
to write code that will go down each cell in column 1 and insert a dynamic
formula in column 2. My code in "CODE 1" below will insert the correct
range in each cell in column 2, but when I try to insert the "YEAR()"
formula in column 2 for each cell, it inserts the correct formula as shown
in Example 2 below, but excel displays the value of the "=YEAR(RC[-1])"
formula as "6/30/1905" instead of "2008".

How can I modify CODE 2 below so for example, cell B1 would equal 2008,
instead of 6/30/1905? As I stated, after running CODE 2 below, the
formulas look like Example 2 below which look correct, but display
6/30/1905 instead of returning 2008 for the year formula result.



CODE 1: *****************

Set c = ActiveSheet.Range("A1")
Do While c < ""
c.Offset(0, 1).Formula = "=RC[-1]"
'set c to the next cell down
Set c = c.Offset(1, 0)
Loop

CODE 2: *****************

Set c = ActiveSheet.Range("A1")
Do While c < ""
c.Offset(0, 1).Formula = "=YEAR(RC[-1])"
'set c to the next cell down
Set c = c.Offset(1, 0)
Loop


Example 1
*******************

A B
1 6/1/2008
2 7/1/2008
3 8/1/2008




Example 2
*******************

A B
1 6/1/2008 =YEAR(A1)
2 7/1/2008 =YEAR(A2)
3 8/1/2008 =YEAR(A3)