Convert dates to text with a Vba macro

[KeldAlmand]

Hi could anyone help me with this problem, I would be very happy.

I want to draw year as TEXT out from dates in a column with Vba

My Test Sheet is like this

Date	Used Formel =TEXT(A1;"åååå")	With VBA macro
10-12-57	1957
24-12-2017	2017
2013-12-02	2013
21-12-2013	2013
24-12-2017	2017
21-12-2013	2013

<tbody>
</tbody>

Here is my code that don't work

Sub FindYearFormDateAndInsertItAsTEXT()
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
' Run trough all rows and set values from Coloumn A as Year, as text, in coloumn C
For i = 2 To FinalRow
If Cells(i, 1) = "" Then
Cells(i, LastCol).Value = "Empty"
Else: Cells(i, LastCol).Formula = TEXT(Cells(i, 1("åååå") ' This line is the tricky part
End If
End Sub

Best regards Keld

 

[Scott Huish]

Does this do what you want?

Sub FindYearFormDateAndInsertItAsTEXT()
Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row).Formula = "=IF(A2="""",""Empty"",TEXT(A2,""yyyy""))"
End Sub

 

[KeldAlmand]

Hi Scott, It works I had just to replace the yyyy with åååå then it does exact what I wanted.
Thank you very much
Best regards Keld