Formulas/Macros on Entire Sheet
 

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.

:)

Formulas/Macros on Entire Sheet
 

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

"DanielleC" wrote in message
...
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.

:)

Formulas/Macros on Entire Sheet
 

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!

"Roger Govier" wrote:

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

"DanielleC" wrote in message
...
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.

:)

Formulas/Macros on Entire Sheet
 

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




"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ...
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
InsertModule
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

"DanielleC" wrote in message
...
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.

:)


__________ 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

Formulas/Macros on Entire Sheet
 

Thanks Ron

Saved me posting my usual warning<g


Gord

On Mon, 16 Mar 2009 17:41:48 +0100, "Ron de Bruin"
wrote:

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

Formulas/Macros on Entire Sheet
 

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

Thanks guys!

"Ron de Bruin" wrote:

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




"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message ...
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
InsertModule
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

"DanielleC" wrote in message
...
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.

:)


__________ 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

Formulas/Macros on Entire Sheet
 

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.

--
Regards
Roger Govier

"Ron de Bruin" wrote in message
...
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




"Roger Govier" <roger@technology4unospamdotcodotuk wrote in message
...
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
InsertModule
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

"DanielleC" wrote in message
...
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.

:)


__________ 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