Formulas/Macros on Entire Sheet

[DanielleC]

I'm not very familiar with using anything other than the very basic formulas
on Excel. I'm using Microsoft Excel 2007 and all I want to do is be able to
take a spreadsheet that is in all caps and convert it to proper. I've tried
using the stupid formula... there's hundreds of cells that need to be
formatted. Please help, I'm beyond frustrated. When I google for help its
like people are talking Chinese... and they're saying go to tools or look for
macros... I see NONE of these things.

For the love of all that is holy...

Help me.

 

[Roger Govier]

Hi Danielle

You will need to use a simple macro as below

Sub ConvertToProper()
Dim c As Range
For Each c In ActiveSheet.UsedRange
c.Value = WorksheetFunction.Proper(c.Value)
Next
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select the sheet where the Capitals appear
Alt+F8 to bring up Macros
Highlight the macro name (ConvertToProper)
Run

I hope this helps. If you are still having difficulties, post back

 

[DanielleC]

MAGIC!!! I have NO idea what just happened but it worked! You just made my
entire week.

Thank you thank you thank you thank you thank you!

 

[Ron de Bruin]

Look out if there are formulas in the range.
They will be values after you run the macro

Use this to avoid this problem
Select the range before you run the code

Sub Propercase_macro()
Dim selectie As Range
Dim cel As Range
On Error Resume Next
Set selectie = Range(ActiveCell.Address & "," & Selection.Address) _
.SpecialCells(xlCellTypeConstants, xlTextValues)
If selectie Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each cel In selectie
cel.Value = StrConv(cel.Value, vbProperCase)
Next cel
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm

Hi Danielle

You will need to use a simple macro as below

Sub ConvertToProper()
Dim c As Range
For Each c In ActiveSheet.UsedRange
c.Value = WorksheetFunction.Proper(c.Value)
Next
End Sub

Copy the Code above
Alt+F11 to invoke the VB Editor
Insert>Module
Paste code into white pane that appears
Alt+F11 to return to Excel

To use
Select the sheet where the Capitals appear
Alt+F8 to bring up Macros
Highlight the macro name (ConvertToProper)
Run

I hope this helps. If you are still having difficulties, post back

--
Regards
Roger Govier




__________ Information from ESET Smart Security, version of virus signature database 3939 (20090316) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 3939 (20090316) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Gord Dibben]

Thanks Ron

Saved me posting my usual warning<g>


Gord

 

[DanielleC]

Oooh, good to know, that should help to... I have a feeling I'll be doing
this a lot.

Thanks guys!

 

[Roger Govier]

Thank you, Ron.
That was very lazy of me to assume that it was only Text on Danielle's
sheet, and not post a full solution.