Change first letter of word to uppercase

Dear Team,

I am having 2 sheet on my workbook ( "Data" & "Sheet3" )

I am typing vale on "Data" and copying to "Sheet3" using =Data!C4 formula where the cells (B5:F6) which is merged.

I want to update all the first letter uppercase on "Sheet3"

I am writing the following code on "Sheet3"

Code:

---

Sub CapsFirstLetter()Dim Cell As Range


With ActiveSheet
    For Each Cell In Application.Intersect(.UsedRange, .Range("B5:F6")).Cells
        Cell.Formula = UCase(Left(Cell.Text, 1)) & LCase(Mid(Cell.Text, 2))
    Next Cell
End With


End Sub

---

But it is not working.

Can any one please help me to solve this

You are probably overthinking this. Try the following

Code:

---

Sub CapsFirstLetter()
Dim oCell As Range
    With Sheets("Sheet3")
        For Each oCell In .Range("B5:F6").Cells
            oCell = UCase(Left(oCell.Text, 1)) & LCase(Mid(oCell.Text, 2))
        Next oCell
    End With
End Sub

---

Dear Graham Mayor,

Thanks for your reply. Your code is working.

But there is one problem

In "Data" my value is M/s PANCHAMI CONCRETE. As per your code it is updating M/s panchami concrete.

But i want it should update M/s Panchami Concrete (upper case of all the first word)

Avoid merged cells at all costs.

Use VBA:

Code:

---

  MsgBox StrConv("aaaa bbbb ccccc", 3)

---

use "Proper" formula

M/s PANCHAMI CONCRETE : =Proper(cell containing the text): M/s Panchami Concrete