Proper case code not working right
 

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

Proper case code not working right
 

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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Juan" wrote in message ...
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

Proper case code not working right
 

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" wrote in message
...
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

Proper case code not working right
 

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
-----Original Message-----
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


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Juan" wrote in message
...
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


.