Proper case code not working right

[Juan]

Hello,
In column C I have some words in All caps and some have
Lower case. I got the following code:
Sub ConvertToProperCase()
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next Rng
End Sub

Before I run this, I select Column C, then I run the
macro, it seems to work but it doesn't stop and goes on an
on and I need to End the program because it takes forever.
So not sure if I have to revised the code. As I mention, i
need to do this is Column C.
Please advise any help.
Thank you,
JUAN

 

[Ron de Bruin]

Try this one Juan

Sub Propercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

See this webpages

http://www.mvps.org/dmcritchie/excel/proper.htm
Or
http://www.cpearson.com/excel/case.htm

 

[Bob Phillips]

Juan,

Try

Sub ConvertToProperCase()
Dim Rng As Range
For Each Rng In Range("C1:C" & Cells(Rows.Count,"C").End(xlUp).Row)
If Rng.HasFormula = False Then
Rng.Value = StrConv(Rng.Value, vbProperCase)
End If
Next Rng
End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[juan]

BOB and Frank,
thanks alot for the revision. I tested both of yours and
both worked great. I really appreciate the great help both
of you have provided.

Once again thanks, and have a great weekend.

Juan