Changing Case in Excel

[scottyjohn]

Hi all,
I need to change the case of a full spreadsheet to UPPER but cant figure out how to do the whole sheet? can anyone help?

John
ski_69@hotmail.com

 

[frainbreeze]

hello

maybe you could either copy and paste into word and changecase there or use Excel's UPPER formula to change things

i.e.
=UPPER(A2) or whatever

though i suppose it'd depend how much data you're dealing with..

good luck with it

frainbreeze


If at first you don't succeed, then skydiving isn't for you.

 

[byundt]

John,
You will need a VBA macro to change both your cell values and formulas. The following sub will make all text strings upper case in constants, and will surround formulas that return text values with the UPPER function. If you want to apply the UPPER function to formulas that may return either a numeric or text result, then just eliminate the If test for error values and numeric results. The sub goes in a regular module sheet.

Sub MakeMeUpper()
Dim cel As Range, rg As Range
Dim frmla As String
Application.ScreenUpdating = False
Set rg = ActiveSheet.UsedRange.SpecialCells(Type:=xlCellTypeConstants)
For Each cel In rg
    If Not IsError(cel) And Not IsNumeric(cel) Then cel = UCase(cel)
Next cel
Set rg = ActiveSheet.UsedRange.SpecialCells(Type:=xlCellTypeFormulas)
For Each cel In rg
    frmla = _
        "=UPPER(" & Replace(cel.Formula, "=", "", 1, 1) & ")"
    If Not IsError(cel) And Not IsNumeric(cel) Then _   'Eliminate this line (but not the next) if all formulas need UPPER function surrounding them.  
        cel.Formula = frmla
Next cel
Application.ScreenUpdating = True
End Sub

Brad