Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Setting formulas for entire columns ... | Excel Discussion (Misc queries) | |||
How to apply macros to entire workbook | Excel Discussion (Misc queries) | |||
Add new formula to existing formulas on entire worksheet | Excel Worksheet Functions | |||
How do assign formulas to an entire column? | Excel Worksheet Functions | |||
Copy the entire sheet to overlay existing sheet? | New Users to Excel |