Uppercase to a cell with numberformat "mmm"

chriscousins

New Member
Joined
Dec 2, 2005
Messages
43
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!
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

chriscousins

New Member
Joined
Dec 2, 2005
Messages
43

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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

New Member
Joined
Dec 2, 2005
Messages
43

ADVERTISEMENT

Nope. Sorry Denis

That just wiped out the cell formula!!

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

BBHRVS

Board Regular
Joined
Nov 3, 2005
Messages
94
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

New Member
Joined
Dec 2, 2005
Messages
43
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,055
Messages
5,569,941
Members
412,299
Latest member
agentless
Top