how do i change all text case in a given worksheet at one time
 

I am attempting to change all the text in a 2,100 line item worksheet at the
same time with the results displaying in the orginal cell that previously had
the data in it but was all upper case. I have tried the proper function and
put a range in instead of the specifc text. The results dislayed in the cell
the proper funtion was written in the last cell only from range I specificed.
Is there a way to do the whole SS at a time

how do i change all text case in a given worksheet at one time
 

Debi, you can with a macro, if you need help using this post back

Sub Proper_Case()
'select the range you want to change
'and run this macro
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

"Debi" wrote in message
...
I am attempting to change all the text in a 2,100 line item worksheet at
the
same time with the results displaying in the orginal cell that previously
had
the data in it but was all upper case. I have tried the proper function
and
put a range in instead of the specifc text. The results dislayed in the
cell
the proper funtion was written in the last cell only from range I
specificed.
Is there a way to do the whole SS at a time

how do i change all text case in a given worksheet at one time
 

Debi

The Proper Function is a cell by cell function like =PROPER(A1) and will not
work on a range.

To change a range you need a macro.

Sub Upper_Case()
'David McRitchie, programming, 2003-03-07
Dim rng1 As Range, rng2 As Range, bigrange As Range
Dim cell As Range
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
On Error Resume Next
Set rng1 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeConstants))
Set rng2 = Intersect(Selection, _
Selection.SpecialCells(xlCellTypeFormulas))
On Error GoTo 0
If rng1 Is Nothing Then
Set bigrange = rng2
ElseIf rng2 Is Nothing Then
Set bigrange = rng1
Else
Set bigrange = Union(rng1, rng2)
End If
If bigrange Is Nothing Then
MsgBox "All cells in range are EMPTY"
GoTo done
End If
For Each cell In bigrange
cell.Formula = UCase(cell.Formula)
Next cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben MS Excel MVP




On Fri, 1 Dec 2006 11:06:00 -0800, Debi wrote:

I am attempting to change all the text in a 2,100 line item worksheet at the
same time with the results displaying in the orginal cell that previously had
the data in it but was all upper case. I have tried the proper function and
put a range in instead of the specifc text. The results dislayed in the cell
the proper funtion was written in the last cell only from range I specificed.
Is there a way to do the whole SS at a time