I have a macro that changes my text to Proper but it cant handles apostrophes, so for example
PETER'S HOUSE
Shoule become Peter's House, but instead the macro changes it to Peter'S House
How can i amend this?
Please Login or Register to view this content.
For your info the full macro is below
Please Login or Register to view this content.
Please Login or Register to view this content.
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Try VBA's StrConv function.
Please Login or Register to view this content.
If posting code please use code tags, see here.
Sixsense, I get a run time error 13, type mismatch on your code
thuis line is highlighted
Norie your code delivers the same error on this linePlease Login or Register to view this content.
Please Login or Register to view this content.
That probably means the area the loop is currently on consists of more than one cell, though you would also get the error if you had a cell with an error value.
If it's the former try this.
Please Login or Register to view this content.
You may be missing the With application and end with which is mentioned in my codeOriginally Posted by nickmax1
Sixthsense,
no i copied and pasted your code in its entirety and still i get that error.
Norie, your code runs no problem but does not solve the apostrophe issue!!
Both the methods works fine at my end.
Norie's StrConv() vba function suggestion is the right method for your case
Are you sure?
It worked with your example and some others I tried?
Can you attach a sample workbook with dummy data?
Last edited by Norie; 05-26-2015 at 07:39 AM.
try this one
The only apostrophes I can find in the workbook are in formulas.
I definitely don't see any in $C$17:$C$190 or $C$193:$C$275, which are the 2 areas the code you posted is looping, those ranges only appear to have numbers.
Mind you if there are apostrophes in the real data it might help if I post the right code.
Please Login or Register to view this content.
aha the "right" code did the trick!
sorry for the test spreadsheet that was an old one I needed to amend the numbers for text. but no matter the code works thanks so much!
Norie/Sixthsense
I noticed after several days that the code doesnt quite work as expected. For the most part its perfect but in instances like the following:
John/PETER - I expect the code to rewrite this as John/Peter but instead i get John/peter
O'malleys - I expect this to be O'Malleys. Instead I get O'malleys
Finally,
s.a.b. Miller I would expect to see S.A.B. Miller but i get S.a.b. Miller
StrConv and Proper aren't 'intelligent' enough to handle cases like that I'm afraid.
May be try using split() to split each words on that cell with space as the delimiter and apply the case sensitivity based on the placement of single quote
nickmax1
If you want to deal with the type of examples you posted you would need to write your own code.
I've had to do that in the past, especially when handling names with apostrophes, eg O'NEILL and Mc/Mac names.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks