Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change from upper case to proper case in excel 2002 | Excel Discussion (Misc queries) | |||
Proper Case | Excel Discussion (Misc queries) | |||
excel'03 how to convert a column from upper case to proper case | Excel Discussion (Misc queries) | |||
Excel: How do I change all upper case ss to proper case? | Excel Worksheet Functions | |||
Changing Upper case to Proper Case | Excel Worksheet Functions |