Formatting a cell's value as a date

[rhythm]

This is a pretty basic task but I can't seem to get it right. I'm trying to take the value in a cell and format it from "1/2012" to "Jan-12" ("mmm-yy") but the format is not applying giving me error messages:


Sub formatthis()
Dim FnlCol As Long
Dim i As Long


FnlCol = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To FnlCol
Cells(1, i).Value = format(Date, "mmm-yy")


Next i

End Sub


Any help, much appreciated!

[CatDaddy]

try
Cells(1, i).Value = Format(Cells(1, i).Text, "mmm-yy")

[Bob Phillips]

An error? Have you checked Tools>References in the VBIDE for any items with MISSING against them?

[Paul_Hossler]

1. The error messages would help

2. Are you sure it's a Date, and not a String? Ctrl-Tilda and see if the "1/2012" is really a number behind the scenes. If it's something like 40321, then that'd be an Excel date

3. I don't understand the part in red



Sub formatthis()
Dim FnlCol As Long
Dim i As Long
FnlCol = Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To FnlCol
Cells(1, i).Value = format(Date, "mmm-yy")
Next i
End Sub


I'd expect something like

Cells(1, i).Value = format(Cells(1, i).Value, "mmm-yy").


4. What you have will make a string out of the date, assumeing that it really is a date

Why not just use .NumberFormat = "mmm-yy"

Possilblyon the whole range?

Paul