=proper

[Scrappy]

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

 

[Paul B]

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

 

[Jake Marx]

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]

Thanks! They both worked great!

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]

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