I have a sheet with a bunch or columns. The values are in upper, lower and
combined case. Some are also numbers. I want to change all valuse to:
first letter capital and the rest lowercase. I have messed around with the
=proper function. The only way I can get this to work is if I put the
function in a blank cell and then only do one cell at a time. Is there away
to do the whole sheet at once and replace the current contents of each cell
with the correct case? Thanks!

Darren
MCP

Darren, you can do it with a macro, like this, select the range you want to
change and then run it

Sub Proper_Case()
'select the range you want to change
'and run this macro
Application.ScreenUpdating = False
Dim Rng As Range
For Each Rng In Selection.Cells
If Rng.HasFormula = False Then
Rng.Value = Application.WorksheetFunction.Proper(Rng.Value)
End If
Next Rng
Application.ScreenUpdating = True
End Sub

--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

"Scrappy" wrote in message
...
I have a sheet with a bunch or columns. The values are in upper, lower
and
combined case. Some are also numbers. I want to change all valuse to:
first letter capital and the rest lowercase. I have messed around with
the
=proper function. The only way I can get this to work is if I put the
function in a blank cell and then only do one cell at a time. Is there
away
to do the whole sheet at once and replace the current contents of each
cell
with the correct case? Thanks!

Darren
MCP

Hi Darren,

You could do this with the following VBA subroutine:

Sub MakeProperCase(rws As Worksheet)
Dim rng As Range

With rws.UsedRange
For Each rng In .Cells
rng.Formula = Application.WorksheetFunction. _
Proper(rng.Formula)
Next rng
End With
End Sub

Just call it like this:

MakeProperCase Worksheets("Sheet1") '/ replace with name of your sheet

This shouldn't mess anything up, but it's a good idea to save your Workbook
before running it, as there's no undoing what VBA has wrought.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Scrappy wrote:
I have a sheet with a bunch or columns. The values are in upper,
lower and combined case. Some are also numbers. I want to change
all valuse to: first letter capital and the rest lowercase. I have
messed around with the =proper function. The only way I can get this
to work is if I put the function in a blank cell and then only do one
cell at a time. Is there away to do the whole sheet at once and
replace the current contents of each cell with the correct case?
Thanks!

Darren
MCP

Thanks! They both worked great!

"Jake Marx" wrote in message
...
Hi Darren,

You could do this with the following VBA subroutine:

Sub MakeProperCase(rws As Worksheet)
Dim rng As Range

With rws.UsedRange
For Each rng In .Cells
rng.Formula = Application.WorksheetFunction. _
Proper(rng.Formula)
Next rng
End With
End Sub

Just call it like this:

MakeProperCase Worksheets("Sheet1") '/ replace with name of your sheet

This shouldn't mess anything up, but it's a good idea to save your
Workbook
before running it, as there's no undoing what VBA has wrought.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Scrappy wrote:
I have a sheet with a bunch or columns. The values are in upper,
lower and combined case. Some are also numbers. I want to change
all valuse to: first letter capital and the rest lowercase. I have
messed around with the =proper function. The only way I can get this
to work is if I put the function in a blank cell and then only do one
cell at a time. Is there away to do the whole sheet at once and
replace the current contents of each cell with the correct case?
Thanks!

Darren
MCP