Change code HELP. Sentence Case

[Corey]

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....

 

[Norman Jones]

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

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....

 

[Corey]

thanks Norman
Too easy

Corey....