Is there a way to have a function auto correct words in ALL CAPS .
 

We have a database that maintains large amounts of data that are in all caps.
When that data is exported to Excel, we would like to be able to run
something in the spreadsheet that decapitalizes the all caps into initial
caps only.

Sub Proper_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 = Application.Proper(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Wed, 17 Nov 2004 11:29:08 -0800, "Kelly Seale" <Kelly
wrote:

We have a database that maintains large amounts of data that are in all caps.
When that data is exported to Excel, we would like to be able to run
something in the spreadsheet that decapitalizes the all caps into initial
caps only.

Mr. Dibben,

Thank you for your time and assistance.
It worked wonderfully.

Regards,
KdS

"Gord Dibben" wrote:

Sub Proper_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 = Application.Proper(Cell.Formula)
Next Cell
done:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Gord Dibben Excel MVP

On Wed, 17 Nov 2004 11:29:08 -0800, "Kelly Seale" <Kelly
wrote:

We have a database that maintains large amounts of data that are in all caps.
When that data is exported to Excel, we would like to be able to run
something in the spreadsheet that decapitalizes the all caps into initial
caps only.