Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 17th 04, 07:29 PM
Kelly Seale
 
Posts: n/a
Default 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.

  #2   Report Post  
Old November 18th 04, 02:00 AM
Gord Dibben
 
Posts: n/a
Default

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.


  #3   Report Post  
Old November 18th 04, 09:05 PM
Kelly Seale
 
Posts: n/a
Default

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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
words and numbers in an IF function Spoony_13 Excel Discussion (Misc queries) 7 December 17th 04 03:29 AM


All times are GMT +1. The time now is 07:22 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017