Uppercase to a cell with numberformat "mmm"

[chriscousins]

I want a cell to either show the month in a numeric style 01,02,03 etc or show the text style "'Jan", "Feb", "Mar" etc

I am using numberformat "mm" and "mmm" depending on the conditions set in the code.

Everything works fine, except I want the text all in capitals "JAN", "FEB", "MAR" etc

Am struggling to work it out....Help appreciated in advance!

 

[Cath]

Does using MMM work instead of mmm?

 

[chriscousins]

No. Tried that!!! Wish it was that simple!!!!

 

[PaddyD]

=UPPER(TEXT(A1,"mmm"))

 

[chriscousins]

None of the above solutions work.

This is the code I am using

If Range("b8") = "CD74" Or Range("b8") = "CD77" Or Range("b8") = "DA32" _
Then
Range("c11").NumberFormat = "mmm"
Range("c15") = upper(Text(c15, "mmm"))
Else
Range("C11").NumberFormat = "mm"
Range("c15").NumberFormat = "mm"
End If

When the numberformat is set to "mmm" it defaults to "Sep", "Oct" etc and I want "SEP", "OCT" etc

 

[SydneyGeek]

Try changing

Range("c15") = upper(Text(c15, "mmm"))

to

Range("c15") = UCase(Format(c15, "mmm"))

The VBA functions are different from the Excel worksheet versions...

Denis

 

[chriscousins]

Nope. Sorry Denis

That just wiped out the cell formula!!

Back to the drawing board. I will not be beaten on this....!!!

 

[BBHRVS]

hi;

Try to put these 2 lines at the end of your macro :

Range("C11") = UCase(Range("C11"))
Range("C15") = UCase(Range("C15"))
and cancel the upper thing.

If Range("b8") = "CD74" Or Range("b8") = "CD77" Or Range("b8") = "DA32" _
Then
Range("c11").NumberFormat = "mmm"
Else
Range("C11").NumberFormat = "mm"
Range("c15").NumberFormat = "mm"
End If
Range("C11") = UCase(Range("C11"))
Range("C15") = UCase(Range("C15"))
End sub

 

[chriscousins]

Sorry BBHRVS

Still no good.

It must be because once I format the cell to "mmm" the default is "Sep" etc, and therefore as long as the cell is formatted that way, I cannot format it again.

Think I'll just leave it be.
Seems hell of a lot of hassle for what appears a simple problem.

Thanks everyone.