Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change code HELP. Sentence Case
The below code changes the text in the designated range to sentence case,
however it ONLY does this for the first word. How can i adapt it to do this to EACH word in the cell? Sub SentenceCase() Dim WS As Worksheet Dim cell As Range For Each WS In ThisWorkbook.worksheets For Each cell In WS.Range("a8:A30,c7:r7") s = cell.Value Start = True For i = 1 To Len(s) ch = Mid(s, i, 1) Select Case ch Case "." Start = True Case "?" Start = True Case "a" To "z" If Start Then ch = UCase(ch): Start = False Case "A" To "Z" If Start Then Start = False Else ch = LCase(ch) End Select Mid(s, i, 1) = ch Next cell.Value = s Next Next End Sub Regards Corey.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change code HELP. Sentence Case
Hi Corey,
Try: '============= Public Sub SentenceCase() Dim WS As Worksheet Dim rng As Range Dim rCell As Range Const sAddress = ("A8:A30,C7:R7") For Each WS In ThisWorkbook.Worksheets On Error Resume Next Set rng = WS.Range(sAddress). _ SpecialCells(xlConstants, xlTextValues) On Error GoTo 0 If Not rng Is Nothing Then For Each rCell In rng.Cells With rCell .Value = Application.Proper(.Value) End With Next rCell End If Next WS End Sub '<<============= --- Regards, Norman "Corey" wrote in message ... The below code changes the text in the designated range to sentence case, however it ONLY does this for the first word. How can i adapt it to do this to EACH word in the cell? Sub SentenceCase() Dim WS As Worksheet Dim cell As Range For Each WS In ThisWorkbook.worksheets For Each cell In WS.Range("a8:A30,c7:r7") s = cell.Value Start = True For i = 1 To Len(s) ch = Mid(s, i, 1) Select Case ch Case "." Start = True Case "?" Start = True Case "a" To "z" If Start Then ch = UCase(ch): Start = False Case "A" To "Z" If Start Then Start = False Else ch = LCase(ch) End Select Mid(s, i, 1) = ch Next cell.Value = s Next Next End Sub Regards Corey.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Change code HELP. Sentence Case
thanks Norman
Too easy Corey.... "Norman Jones" wrote in message ... Hi Corey, Try: '============= Public Sub SentenceCase() Dim WS As Worksheet Dim rng As Range Dim rCell As Range Const sAddress = ("A8:A30,C7:R7") For Each WS In ThisWorkbook.Worksheets On Error Resume Next Set rng = WS.Range(sAddress). _ SpecialCells(xlConstants, xlTextValues) On Error GoTo 0 If Not rng Is Nothing Then For Each rCell In rng.Cells With rCell .Value = Application.Proper(.Value) End With Next rCell End If Next WS End Sub '<<============= --- Regards, Norman "Corey" wrote in message ... The below code changes the text in the designated range to sentence case, however it ONLY does this for the first word. How can i adapt it to do this to EACH word in the cell? Sub SentenceCase() Dim WS As Worksheet Dim cell As Range For Each WS In ThisWorkbook.worksheets For Each cell In WS.Range("a8:A30,c7:r7") s = cell.Value Start = True For i = 1 To Len(s) ch = Mid(s, i, 1) Select Case ch Case "." Start = True Case "?" Start = True Case "a" To "z" If Start Then ch = UCase(ch): Start = False Case "A" To "Z" If Start Then Start = False Else ch = LCase(ch) End Select Mid(s, i, 1) = ch Next cell.Value = s Next Next End Sub Regards Corey.... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
convert text to sentence case | Excel Discussion (Misc queries) | |||
Sentence Case | Excel Discussion (Misc queries) | |||
How do I change case to sentence case in groups in excel? | Excel Discussion (Misc queries) | |||
How to change case on a spreadsheet from Caps to Sentence? | Excel Discussion (Misc queries) | |||
Change Capitals to Sentence Case | Excel Discussion (Misc queries) |