Solved: Date in all Caps

[zoom38]

I'm trying to get the date to be in upper case letters. Specifically the line with UCase below. For some reason it doesn't work here. Can anyone advise what i'm doing wrong.

Thanks
Gary


Sub NamePers()
Dim Sheet As Long
Dim MyDate As Date

MyDate = Worksheets("RVSD Squad 1").Cells(1, 19).Value
For Sheet = 8 To 35
Sheets(Sheet).Name = Format(MyDate, "mmm d")
Sheets(Sheet).Cells(7, 10).Value = UCase(Format(MyDate, "mmm d, yyyy"))
MyDate = MyDate + 1
Next Sheet

End Sub

[geekgirlau]

Sub NamePers()
Dim Sheet As Long
Dim MyDate As Date

MyDate = Worksheets("RVSD Squad 1").Cells(1, 19).Value
For Sheet = 8 To 35
Sheets(Sheet).Name = Format(MyDate, "mmm d")
Sheets(Sheet).Cells(7, 10).Formula = _
"=upper(text(datevalue(""" & MyDate & """), ""mmm d, yyyy""))"
MyDate = MyDate + 1
Next Sheet
End Sub

[RichardSchollar]

Gary

Perhaps it is Excel automatically converting what it recognises as a Textual date to a numeric? Anyway, try setting the format of the cell first:

Sub NamePers()
Dim Sheet As Long
Dim MyDate As Date

MyDate = Worksheets("RVSD Squad 1").Cells(1, 19).Value
For Sheet = 8 To 35
Sheets(Sheet).Name = Format(MyDate, "mmm d")
With Sheets(Sheet).Cells(7, 10)
.NumberFormat = "@"
.Value = UCase(Format(MyDate, "mmm d, yyyy"))
End With
MyDate = MyDate + 1
Next Sheet

End Sub

Hope this helps!

Richard

[Bob Phillips]

Sub NamePers()
Dim Sheet As Long
Dim MyDate As Date

MyDate = Worksheets("RVSD Squad 1").Cells(1, 19).Value
For Sheet = 8 To 35
Sheets(Sheet).Name = Format(MyDate, "mmm d")
Sheets(Sheet).Cells(7, 10).Value = "'" & UCase(Format(MyDate, "mmm d, yyyy"))
MyDate = MyDate + 1
Next Sheet

End Sub

[zoom38]

Thank you all for responding.

geekgirl, a problem I ran into with your solution was that on each worksheet in cell(7,10) the formula would be visible and not the result. When I clicked on the = sign in the edit formula window the correct result would come up but still only the formula would be visible in the cell itself.

Richard, your solution worked without a hitch.

XLD, I will be using your solution. It worked without a hitch also and has the least amount of changes.

Thanks Again
Gary