How can I change the case of letters without using function ?

[Rahim]

I am preparing a report in MS Excel with many sheets. I have typed it all
in capital letters. Now, I need to chage it to lower cases with the first
letter in capital. Pls tell me a tip to solve it.

 

[RB Smissaert]

Try something like this:

Sub test()

Dim sh As Worksheet
Dim rng As Range
Dim c As Range

For Each sh In ThisWorkbook.Worksheets
With sh
Set rng = Range(.Cells(1), _
.Cells(1).SpecialCells(xlLastCell))
For Each c In rng.Cells
If Not IsEmpty(c) Then
c.Value = _
Application.WorksheetFunction.Proper(c.Value)
End If
Next c
End With
Next sh

End Sub


RBS

 

[Mike H]

Hi,

Well you don't give too much detail but this may work for you

=proper(a1)

where A1 is the word to convert. If there are multiple words in A1 it will
capitalise the first letter of each word which may not be what you want.

Mike

 

[Peter T]

If, as Mike speculates, you only want the first word capitalized search this
group for "sentence case".

Regards,
Peter T

 

[Gord Dibben]

I would try something like that if I wanted all formulas on all sheets wiped
out.

For one sheet I would use this to change case and preserve formulas, if any
present.

Sub Proper_Case()
Dim rng As Range
Set rng = Nothing
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If rng Is Nothing Then
Else
rng.Formula = Application.Proper(rng.Formula)
End If
End Sub


Gord Dibben MS Excel MVP

 

[RB Smissaert]

Sure, I hardly ever use formula's so tend to overlook that and thanks for
the correction.

RBS

 

[Gord Dibben]

I view it as "just in case" code.

No pun intended.

Gord

 

[Harlan Grove]

For one sheet I would use this to change case and preserve formulas,
if any present.

Sub Proper_Case()
Dim rng As Range
Set rng = Nothing
On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0
If rng Is Nothing Then
Else
rng.Formula = Application.Proper(rng.Formula)
End If
End Sub

....

Random indentation?

This macro will replace all cells containing text constants with the
proper case value of the first cell in the range SpecialCells returns.
For example, if C1 contained foo and A3 contained bar, running this
macro would produce Foo in BOTH C1 AND A3. Unlikely that's what the OP
wants.

Better to stick with iterating through individual cells within the
selected worksheets.


Sub foo()
Dim c As Range, rng As Range, ws As Worksheet

For Each ws In ActiveWindow.SelectedSheets
On Error Resume Next
Set rng = ws.UsedRange.SpecialCells(xlCellTypeConstants)

If Err.Number = 0 Then
For Each c In rng
c.Formula = Application.WorksheetFunction.Proper(c.Formula)
Next c

Else
Err.Clear

End If

Next ws

End Sub

 

[Dana DeLouis]

Just to mention another option. Proper can't evaluate multiple areas, but
it can do a single "Area."

Sub Proper_Case()
Dim rng As Range
Dim Grp As Range

On Error Resume Next
Set rng = Cells.SpecialCells(xlCellTypeConstants)
On Error GoTo 0

If Not rng Is Nothing Then
For Each Grp In rng.Areas
Grp.Value = Application.Proper(Grp.Value)
Next Grp
Else
'Do Nothing
End If
End Sub