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.

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



"Rahim" <(E-Mail Removed)> wrote in message
news:7FF02107-65A0-4396-BC06-(E-Mail Removed)...
>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.

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

"Rahim" wrote:

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

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

Regards,
Peter T


"Mike H" <(E-Mail Removed)> wrote in message
news:75A4C2F8-A5DE-4C1E-8225-(E-Mail Removed)...
> 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
>
> "Rahim" wrote:
>
> > 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.

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

On Sun, 17 Feb 2008 11:50:06 -0000, "RB Smissaert"
<(E-Mail Removed)> wrote:

>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
>
>
>
>"Rahim" <(E-Mail Removed)> wrote in message
>news:7FF02107-65A0-4396-BC06-(E-Mail Removed)...
>>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.

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

RBS

"Gord Dibben" <gorddibbATshawDOTca> wrote in message
news:(E-Mail Removed)...
>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
>
> On Sun, 17 Feb 2008 11:50:06 -0000, "RB Smissaert"
> <(E-Mail Removed)> wrote:
>
>>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
>>
>>
>>
>>"Rahim" <(E-Mail Removed)> wrote in message
>>news:7FF02107-65A0-4396-BC06-(E-Mail Removed)...
>>>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.
>

I view it as "just in case" code.

No pun intended.

Gord

On Sun, 17 Feb 2008 18:14:38 -0000, "RB Smissaert"
<(E-Mail Removed)> wrote:

>Sure, I hardly ever use formula's so tend to overlook that and thanks for
>the correction.
>
>RBS
>
>"Gord Dibben" <gorddibbATshawDOTca> wrote in message
>news:(E-Mail Removed)...
>>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
>>
>> On Sun, 17 Feb 2008 11:50:06 -0000, "RB Smissaert"
>> <(E-Mail Removed)> wrote:
>>
>>>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
>>>
>>>
>>>
>>>"Rahim" <(E-Mail Removed)> wrote in message
>>>news:7FF02107-65A0-4396-BC06-(E-Mail Removed)...
>>>>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.
>>

Gord Dibben <gorddibbATshawDOTca> wrote...
....
>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

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

--
Dana DeLouis


"Harlan Grove" <(E-Mail Removed)> wrote in message
news:3d05690e-d5a8-4395-bdb0-(E-Mail Removed)...
> Gord Dibben <gorddibbATshawDOTca> wrote...
> ...
>>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