Uppercase Letter After Each Space

[christian2016]

Hi Guys,

Need help with a VBA to uppercase each letter after a space.
Also including the first letter in the string.

Example:
hello my name is bob smith

VBA code will convert the string to:
Hello My Name Is Bob Smith

This will be done on all cells in a range starting in Cell E3 up until the last used cell with text.

Any Help is greatly appreciated.

Thanks

 

[KevCarter]

Here is a utility procedure I found that works on a range of cells.

Sub ProperMacro()


    Dim CaseRange As Range
    Dim CalcMode As Long
    Dim Cell As Range


    On Error Resume Next
    Set CaseRange = Intersect(Selection, _
        Selection.Cells.SpecialCells(xlCellTypeConstants, xlTextValues))
    On Error GoTo 0
    
    If CaseRange Is Nothing Then Exit Sub


        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
            .EnableEvents = False
        End With
    
        For Each Cell In CaseRange.Cells
            Cell.Value = StrConv(Cell.Value, vbProperCase)
        Next Cell
    
        With Application
            .ScreenUpdating = True
            .EnableEvents = True
            .Calculation = CalcMode
        End With
        
End Sub

 

[Fennek]

Hi,

if the texts are in A1:A2

Sub Main
for i = 1 to 2
    cells(i,2) = join(worksheetfunction.proper(split(cells(i,1)))
next i
End Sub

regards

 

[christian2016]

Thanks for the help.

I have changed the code as per below.

This works well.

Also how do i add in Uppercase after ' and - Is this possible

Sub test()

Dim Cell As Range
Dim CaseRange As Range


 LastRow = Cells(Rows.Count, "E").End(xlUp).Row
 
 Set Cell = Range("E3:E" & LastRow)
  Set CaseRange = Range("E3:E" & LastRow)
  




   For Each Cell In CaseRange.Cells
            Cell.Value = StrConv(Cell.Value, vbProperCase)
        Next Cell


End Sub

 

[lhartono]

Try this as you requested,

[COLOR=#0000cd]Sub SetProper()
[/COLOR][COLOR=#d3d3d3]'by lhartono[/COLOR][COLOR=#0000cd]
Dim rngString   As Range
Dim cell        As Range

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
End With

Set rngString = Sheets("YourSheetName").Range("E3", "E" & Range("E" & Rows.Count).End(xlUp).Row)

For Each cell In rngString.Cells
    cell = VBA.StrConv(cell, vbProperCase)
Next

Set rngString = Nothing

With Application
    .Calculation = xlCalculationAutomatic
    .EnableEvents = True
    .ScreenUpdating = True
End With

End Sub[/COLOR][COLOR=#0000cd][/COLOR][COLOR=#0000cd][/COLOR]