Upper Case Vs: Lower case

[Bill Carpenter]

Each month I receive a spreadsheet approx 3000 lines long. A column is the
"city" name. It downloads all different ways. Some all CAPS some case
sensitive some all lower case. Each month I correct each city manually
typing it correctly. Is there a way I can highlight the column and do this
all at once, so fror example it would convert to San Francisco? This is
taking much to long the manual way. Thanks for any tips or help.

[Anne Troy]

Hi, Bill. Use this macro:
http://vbaexpress.com/kb/getarticle.php?kb_id=69
But change this line: Rng.Value = UCase(Rng.Value)
To this: Rng.Value = StrConv(Rng.Value, vbProperCase)
************
Anne Troy
www.OfficeArticles.com

"Bill Carpenter" <[email protected]> wrote in message
news:[email protected]...
> Each month I receive a spreadsheet approx 3000 lines long. A column is
> the
> "city" name. It downloads all different ways. Some all CAPS some case
> sensitive some all lower case. Each month I correct each city manually
> typing it correctly. Is there a way I can highlight the column and do
> this
> all at once, so fror example it would convert to San Francisco? This is
> taking much to long the manual way. Thanks for any tips or help.

[bj]

check out the proper() function in help

"Bill Carpenter" wrote:

> Each month I receive a spreadsheet approx 3000 lines long. A column is the
> "city" name. It downloads all different ways. Some all CAPS some case
> sensitive some all lower case. Each month I correct each city manually
> typing it correctly. Is there a way I can highlight the column and do this
> all at once, so fror example it would convert to San Francisco? This is
> taking much to long the manual way. Thanks for any tips or help.

[Bill Carpenter]

Absolutely amazing. But that is Excel. I had time to do this this morning,
and worked like a charm. Is there some way I can save the macro in
Tools-macro-macro for all work books, and not have to copy each time? Thanks
so much!

"bj" wrote:

> check out the proper() function in help
>
> "Bill Carpenter" wrote:
>
> > Each month I receive a spreadsheet approx 3000 lines long. A column is the
> > "city" name. It downloads all different ways. Some all CAPS some case
> > sensitive some all lower case. Each month I correct each city manually
> > typing it correctly. Is there a way I can highlight the column and do this
> > all at once, so fror example it would convert to San Francisco? This is
> > taking much to long the manual way. Thanks for any tips or help.

[Gord Dibben]

Bill

Global macros can be saved in your Personal.xls or in an add-in.

Personal.xls is created the first time you record a Macro using Macro
Recorder.

It is used for making macros available to all open workbooks.

Tools>Macro>Record New Macro. A dialog box will come up asking you name the
macro and where to place it. Pick Personal Macro Workbook from the dropdown.
Copy and paste a couple of cells then Stop Recording.

You now have a Personal.xls in your Office\XLSTART folder. You can go to
Visual Basic Editor(Alt+F11) to view the macro you just recorded in a Module.

You can add more macros by recording or by typing/copying them into the
Module.

In this case, copy the code into the module.

You can do a File>Save from there or better yet hit ALT + Q to return to the
Excel window.

Then with Personal.xls active, hit Window>Hide.

When you close Excel you will be asked if you want to save Personal.xls. Yes!

It will open hidden next time you start Excel.

NOTE: when assigning macros to buttons or menu items you will have to precede
the macro name with Personal.xls.

i.e. Personal.xls!macroname

An alternative to Personal.xls is to create an add-in(*.xla) with your macros
in it and load it through Tools>Add-ins.

The benefit of this is that you don't have to precede the macro name with the
filename.

A disadvantage is that you will not see the macros in the Tools>Macro>Macros
dialog.


Gord Dibben Excel MVP



On Tue, 20 Sep 2005 09:46:02 -0700, "Bill Carpenter"
<[email protected]> wrote:

>Absolutely amazing. But that is Excel. I had time to do this this morning,
>and worked like a charm. Is there some way I can save the macro in
>Tools-macro-macro for all work books, and not have to copy each time? Thanks
>so much!
>
>"bj" wrote:
>
>> check out the proper() function in help
>>
>> "Bill Carpenter" wrote:
>>
>> > Each month I receive a spreadsheet approx 3000 lines long. A column is the
>> > "city" name. It downloads all different ways. Some all CAPS some case
>> > sensitive some all lower case. Each month I correct each city manually
>> > typing it correctly. Is there a way I can highlight the column and do this
>> > all at once, so fror example it would convert to San Francisco? This is
>> > taking much to long the manual way. Thanks for any tips or help.