Sorry, I hit Post on the previous question accidently.....

To repeat, I am trying to extract the year only in a cell based on the date
in another cell. What I have in vba is:

Range("J4").Select
ActiveCell.FormulaR1C1 = "=text(b4, "yyyy")"

This returns an error: Compile Error: Expected: end of statement

What am I doing wrong.

Thanks
--
Linda

Range("J4").Formula = "=text(b4, ""yyyy"")"

When working with formula in VBA, one good apporach is to get the formula working, trurn on macro
recording, select the cell, press F2 and enter, then stop recording, and look at the code. That will
handle the double quotes (which need to be doubled within a formula) correctly.

HTH,
Bernie
MS Excel MVP


"mathel" wrote in message
...
Sorry, I hit Post on the previous question accidently.....

To repeat, I am trying to extract the year only in a cell based on the date
in another cell. What I have in vba is:

Range("J4").Select
ActiveCell.FormulaR1C1 = "=text(b4, "yyyy")"

This returns an error: Compile Error: Expected: end of statement

What am I doing wrong.

Thanks
--
Linda

You have used the FormulaR1C1 property, which expects the range in R1C1
notation, BUT you used A1 notation in the formula. You should use

ActiveCell.Formula = "=TEXT(B4,"yyyy")"

not FormulaR1C1.


On Mon, 5 Nov 2007 05:17:03 -0800, mathel
wrote:

Sorry, I hit Post on the previous question accidently.....

To repeat, I am trying to extract the year only in a cell based on the date
in another cell. What I have in vba is:

Range("J4").Select
ActiveCell.FormulaR1C1 = "=text(b4, "yyyy")"

This returns an error: Compile Error: Expected: end of statement

What am I doing wrong.

Thanks