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!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,128
Messages
5,835,564
Members
430,367
Latest member
glastonbury

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top