Year date format

[Guest]

I need to extract the year only in a cell from the date in another cell.
What I have tried is:

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

I get an error which reads: Compile error:

 

[Zone]

Activecell.Formula="=YEAR(B4)"

 

[Guest]

I somehow lost the entire macro in the file I was working on. I now have to
wait until our IT people can restore it to try your resolution to the
problem. I will post a 'Yes' or 'No' once I have tested my document.

Thanks for the quick response.

 

[Guest]

Hi,

I input the line exactly as shown below, then run my macro. The results I
get are the cell shows: #NAME?

When I look at the actual formula it appears as: =YEAR('B4').
How do I get rid of the single quote around B4?

Thanks

 

[Dave Peterson]

Zone used: Activecell.Formula = ...
I suspect you're still using: ActiveCell.FormulaR1C1 = ...

Try using Activecell.Formula instead.

 

[Guest]

I did not leave a space between ActiveCell.Formula="YEAR(B4)". I tried it
again, with a space. The results I obtained in the cell were 1905. The
exact code I am using is as follows:

Range("B4").Select
ActiveCell.FormulaR1C1 = "=TODAY()"

Range("J4").Select
ActiveCell.Formula = "=YEAR(B4)"

As stated, this gives me a result of 1905 in cell J4.

Thanks for your help

 

[Guest]

I just posted a reply indicating there was still a problem - I just figured
it out. I had formatted cell J4 to Custom formatting as 'yyyy'. When I ran
the macro with

Range("J4").Select
ActiveCell.Formula = "=YEAR(B4)"

I was getting the result of 1905. I have now formatted the cell as number
and now get the correct response.

Thanks for all you assistance.
Linda

 

[Dave Peterson]

Glad you got it fixed.

You can accomplish the same kind of thing without the .select's:

Range("B4").FormulaR1C1 = "=TODAY()"
Range("J4").Formula = "=YEAR(B4)"

or even

with worksheets("Somesheetnamehere")
.Range("B4").Formula = "=TODAY()"
with .range("J4")
.numberformat = "General"
.Formula = "=YEAR(B4)"
end with
end with