Changing a spreadsheet, row, or column into all caps.
Posted by Hugh on November 08, 2000 7:01 AM
Is it possible to replace or change all letters in a spreadsheet into all Upper case with a "quick method".
I have Excel 95 and thought I could do it with the replace function.
Thanks,
Hugh
Posted by Tim Francis-Wright on November 08, 2000 3:10 PM
The following macro should do what you want; it puts in all caps each cell in the current
selection.
Sub macro()
Dim cel As Object
Dim foo As String, bar As String
For Each cel In Selection
foo = cel.Formula
if foo <> "" then
bar = Format(foo, ">")
cel.Formula = bar
end if
Next cel
End Sub
This doesn't work very well for an entire sheet:
in that case, use ActiveSheet.UsedRange instead
of Selection.
HTH
Posted by Celia on November 08, 2000 4:12 PM
For Each cel In Selection foo = cel.Formula if foo <> "" then bar = Format(foo, ">") cel.Formula = bar end if Next cel
As suggested by Tim, the following should work whether the selection is the whole sheet or some smaller range(s):-
Dim rng As Range, cel As Range
Set rng = Intersect(ActiveSheet.UsedRange, Selection)
For Each cel In rng
If cel.Formula <> "" Then
cel.Formula = Format(cel.Formula, ">")
End If
Next cel
Celia
