Hi
1) How do i select a worksheet and turn all text to Proper Text?

2) How do i select a column and turn all text in it to Proper Text?

Thank you

Hi,

Right click your sheet tab, view code and paste the code below in. Select
the column/row or block of text and run the code

Sub proper()
For Each c In Selection
c.Formula = WorksheetFunction.proper(c.Formula)
Next c
End Sub

Mike

"Withnails" wrote:

Hi
1) How do i select a worksheet and turn all text to Proper Text?

2) How do i select a column and turn all text in it to Proper Text?

Thank you

Sub Macro()
For Each cell In ActiveSheet.UsedRange
cell.Value = StrConv(cell, vbProperCase)
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Withnails" wrote:

Hi
1) How do i select a worksheet and turn all text to Proper Text?

2) How do i select a column and turn all text in it to Proper Text?

Thank you

Jacob

I would trap for formulas so's if any they don't get converted to values.

Sub Macro()
For Each cell In ActiveSheet.UsedRange
If Not cell.HasFormula Then
cell.Value = StrConv(cell, vbProperCase)
End If
Next
End Sub


Gord Dibben MS Excel MVP

On Tue, 8 Sep 2009 07:56:10 -0700, Jacob Skaria
wrote:

Sub Macro()
For Each cell In ActiveSheet.UsedRange
cell.Value = StrConv(cell, vbProperCase)
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Withnails" wrote:

Hi
1) How do i select a worksheet and turn all text to Proper Text?

2) How do i select a column and turn all text in it to Proper Text?

Thank you

1) Here is a method that does use a loop...

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Cells
.Value = Application.Proper(.Value)
End With
End Sub

Just change my example worksheet name ("Sheet3") to the actual name of the
worksheet that you want to do this on.

2) Same set up except we change the Cells reference to the Column
reference...

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Columns("E")
.Value = Application.Proper(.Value)
End With
End Sub

Change the worksheet name as in #1 above and also change the column letter
from my example of "E" to the actual column letter or number.

--
Rick (MVP - Excel)


"Withnails" wrote in message
...
Hi
1) How do i select a worksheet and turn all text to Proper Text?

2) How do i select a column and turn all text in it to Proper Text?

Thank you

1) Here is a method that does use a loop...

The line above should have said...

1) Here is a method that does NOT use a loop...


--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
1) Here is a method that does use a loop...

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Cells
.Value = Application.Proper(.Value)
End With
End Sub

Just change my example worksheet name ("Sheet3") to the actual name of the
worksheet that you want to do this on.

2) Same set up except we change the Cells reference to the Column
reference...

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Columns("E")
.Value = Application.Proper(.Value)
End With
End Sub

Change the worksheet name as in #1 above and also change the column letter
from my example of "E" to the actual column letter or number.

--
Rick (MVP - Excel)


"Withnails" wrote in message
...
Hi
1) How do i select a worksheet and turn all text to Proper Text?

2) How do i select a column and turn all text in it to Proper Text?

Thank you

Warning: The code I posted has the same defect that Gord pointed out in
Jacob's code... it will convert formulas to text, so you may not want to use
what I posted.

I'm not sure I can salvage a non-looping method given this problem, but I
will look at it to see.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
1) Here is a method that does use a loop...

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Cells
.Value = Application.Proper(.Value)
End With
End Sub

Just change my example worksheet name ("Sheet3") to the actual name of the
worksheet that you want to do this on.

2) Same set up except we change the Cells reference to the Column
reference...

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Columns("E")
.Value = Application.Proper(.Value)
End With
End Sub

Change the worksheet name as in #1 above and also change the column letter
from my example of "E" to the actual column letter or number.

--
Rick (MVP - Excel)


"Withnails" wrote in message
...
Hi
1) How do i select a worksheet and turn all text to Proper Text?

2) How do i select a column and turn all text in it to Proper Text?

Thank you

Sub FixCase()
With Worksheets("Sheet3").UsedRange.Cells
.Formula = Application.Proper(.Formula)
End With
End Sub


Gord

On Tue, 8 Sep 2009 16:13:27 -0400, "Rick Rothstein"
wrote:

Warning: The code I posted has the same defect that Gord pointed out in
Jacob's code... it will convert formulas to text, so you may not want to use
what I posted.

I'm not sure I can salvage a non-looping method given this problem, but I
will look at it to see.

I was going to post that, but it also has a potential hidden problem... any
quoted text within a formula will be changed to proper case.

--
Rick (MVP - Excel)


"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Sub FixCase()
With Worksheets("Sheet3").UsedRange.Cells
.Formula = Application.Proper(.Formula)
End With
End Sub


Gord

On Tue, 8 Sep 2009 16:13:27 -0400, "Rick Rothstein"
wrote:

Warning: The code I posted has the same defect that Gord pointed out in
Jacob's code... it will convert formulas to text, so you may not want to
use
what I posted.

I'm not sure I can salvage a non-looping method given this problem, but I
will look at it to see.

Yup


Gord

On Tue, 8 Sep 2009 17:07:01 -0400, "Rick Rothstein"
wrote:

I was going to post that, but it also has a potential hidden problem... any
quoted text within a formula will be changed to proper case.