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.... |
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.... |
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.... |
All times are GMT +1. The time now is 08:57 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com