Insert Dynamic Formula
 

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)

Insert Dynamic Formula
 

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)

Insert Dynamic Formula
 

Problem is your year "2008" is being formatted as a date which is 6/30/1905.
If you update the format for Column B to be "General" it should resolve your
issue.

c/Pffset(0,1).NumberFormat = "General"

Thanks,
Dan

"Scott" wrote:

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)