UPPER CASE

  • Thread starter Thread starter JASON
  • Start date Start date
Jason, here is one way, select your range and run this

Sub CAPS()
'select range and run this to change to all CAPS
Dim cel As Range
For Each cel In Intersect(Selection, _
ActiveSheet.UsedRange)
cel.Formula = UCase$(cel.Formula)
Next
End Sub


--
Paul B
Always backup your data before trying something new
Using Excel 2000 & 97
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **
 
Jason,
following should work in most situations.

if 1 cell is selected it picks the current region,
else it processes the text values in the selection.

Sub MakeUpper()
Dim c As Range
on error resume next 'to avoid prob if no text cells are found
If TypeName(Selection) <> "Range" Then Beep: Exit Sub
If Selection.Count = 1 Then Selection.CurrentRegion.Select
With Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
For Each c In .Cells
c = UCase(c)
Next
End With
End Sub


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
 
You've made a single cell selection expand to the current region.
That is the default and exactly what I would try to avoid.

The important thing is that you have limited the cells to the used range
and prevented formulas from being converted to values. Paul's
solution prevents formulas from being changed to values by assigning
a formula to a formula, either way prevents loss of formulas..

The following includes a few more things to make the macro run
faster, like turning off screen updating and calculation.

http://www.mvps.org/dmcritchie/excel/proper.htm#upper


"keepitcool" <[email protected]
 
Back
Top