I have made several work sheets with clients details on eg, name, address etc. In some of the cells text is all uppercase and in others lower.
How do I create the entire work sheet into capitalization (first letter capital with the rest of text lower) without using PROPER function, where it puts the result in another cell.
Any help would be greatful as I am taking forever to work this one out, I would of thought this would be a simple solution to achieve but Im finding it not.
wideboards
Perhaps:
Please Login or Register to view this content.
Regards
Jon (Excel 2003, 2007, 2010, 2013)
Possibly using Evaluate method ?
Very basic example I'm afraid as I'm heading off for lunch...
EDIT - in essence the above is a reiteration of JvH's post...Please Login or Register to view this content.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Try this code. It will apply the PROPER worksheet function to the range you have selected.
Please Login or Register to view this content.
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
But without the loop. Very clever Luke!Originally Posted by DonkeyOte
Possibly using Evaluate method ?
Very basic example I'm afraid as I'm heading off for lunch...
EDIT - in essence the above is a reiteration of JvH's post...Please Login or Register to view this content.
I can't take credit for that - I picked it up from Pedro (c/o Bill Jelen's book)... moreover my quick demo makes assumptions of there being no formulae etc... to limit processing as much as possible (and so as to avoid overriding formulae) perhaps:
Please Login or Register to view this content.
The use of SpecialCells(xlCellTypeConstants, xlTextValues) is imperative because:
1. it reduces the size of the range to loop through (which can be avoided using Lukes method)
and
2. because without it, it will convert formula to constants.
Last edited by JONvdHeyden; 06-24-2009 at 08:28 AM.
Where would I put this code and how? I am very new to this or I have just simply forgot.
Simple instructions please.
Thank you, Ive successfully done it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks