Upper Case Macro

[Phil H]

I need a macro, which will be attached to a button, to change text to upper
case for any range of selected cells. Can someone help?

 

[Mike H]

Hi,

Attach this to a button

Sub Marine()
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Mike

 

[Sam Wilson]

Sub upper()

Dim r As Range
For Each r In Selection
r.Value = UCase(r.Value)
Next r

End Sub

 

[Phil H]

I added the Dim line, but get compile error:
"For Each control variable must be Variant or Object."

Sub ChangeToUpperCase()
Dim c As String
For Each c In Selection
c.Formula = UCase(c.Formula)
Next
End Sub

Former submariner?

 

[Mike H]

Sam,

Using .value is taking an unnecessary risk. What it will do is change any
formula selected (maybe by accident) to values. Use .formula instead or check
using something like

If Not r.HasFormula Then r.Value = UCase(r.Value)

Mike

 

[Mike H]

What did you dim it as ? It should be

Dim c as Range

Mike

 

[Sam Wilson]

True, but imagine

A1 = "BlahBlahBlah"
B1 = "=A1"

If you use ucase(Range("B1").formula) you won't end up with BLAHBLAHBLAH

Sam

 

[Rick Rothstein]

One possible problem with your method... if the one or more cells in the
selection have formulas in them, and if those formulas have quoted text in
them, then the quoted text will all be converted to upper case as well. I
think the idea you posted to Sam may be the way to go here...

Sub Marine()
Dim C As Range
For Each C In Selection
If Not C.HasFormula Then C.Value = UCase(C.Value)
Next
End Sub

 

[Sam Wilson]

If Not r.HasFormula Then
r.Value = UCase(r.Value)
else
r.formula = "=UPPER(" & right(r.formula,len(r.formula)-1) & ")"
end if

maybe

 

[Sam Wilson]

If the formula returns a lower case text string then this won't work. If you
try to insert =Upper(<old formula>) in the macro you convert numbers to
text...

=IF(ISNUMBER(<old formula>),VALUE(<old formula>),UPPER(<old formula>))

could work though, but it's an ugly solution.

I think the only solution is to use a font like Felix Titling.

 

[Phil H]

Thands guys for your input - appreciate it. So what is the final macro?

 

[Chip Pearson]

Try code like the following:


Sub AAA()
Dim RR As Range
Dim R As Range
On Error GoTo ErrH:
Set RR = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
.EnableEvents = False
End With

For Each R In RR.Cells
R.Value = UCase(R.Value)
Next R
ErrH:
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

Select the range of cells to change to upper case and run the code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





On Thu, 10 Sep 2009 07:18:01 -0700, Phil H <Phil

 

[Rick Rothstein]

Well I guess this macro would handle all of the concerns raised in this
thread...

Sub UpperCaseRange()
Dim C As Range
For Each C In Selection
If Not C.HasFormula Then
C.Value = UCase(C.Value)
ElseIf IsNumeric(C.Text) Then
C.Value = UCase(C.Value)
Else
C.Formula = "=UPPER(" & Mid(C.Formula, 2) & ")"
End If
Next
End Sub

 

[Phil H]

Thanks, Rick. Works as expected.

Well I guess this macro would handle all of the concerns raised in this
thread...

Sub UpperCaseRange()
Dim C As Range
For Each C In Selection
If Not C.HasFormula Then
C.Value = UCase(C.Value)
ElseIf IsNumeric(C.Text) Then
C.Value = UCase(C.Value)
Else
C.Formula = "=UPPER(" & Mid(C.Formula, 2) & ")"
End If
Next
End Sub

 

[Phil H]

Thanks, Chip. I kept this macro as well as Rick's. Your macro changes all
cells in the worksheet to UC. Useful.

 

[Chip Pearson]

Your macro changes all
cells in the worksheet to UC.

Yeah, that is due to a problem (about which I had forgotten) with
SpecialCells when no cells are found and you have a selection of a
single cell. SpecialCells erroneously uses the entire sheet. You can
change

Set RR = Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues)

to

Set RR = Application.Intersect( _
Selection, _
Selection.SpecialCells( _
xlCellTypeConstants, xlTextValues))

to work around the problem. This still has the advantage that it only
looks at cells without formulas and that contain text, as opposed to
numeric, values. It takes the test for HasFormula and the
unnecessary conversion of numeric values out of the code and wraps
them more efficiently up in the SpecialCells test.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)