Cap everyword in each cell of a column - macro issue

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
Need help with this:

I want to cap the first letter of every word within a cell, NOT just the first word.

=proper(A) doesn't work for me because if a word has an 's at the end such as Physician's, it will also cap that s so it ends up as Physician'S.

The following program works great for me with one problem. It caps each word correctly but I have to hit escape to get it to stop working and go on to other functions otherwise cannot stop it from continuing on after it does the job.

Can someone help me find the brakes to make it stop spinning?

Sub ToProperA()
Dim a As Range
For Each a In Selection
a.Value = StrConv(a.Value, vbProperCase)
Next a
End Sub
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
the entire column......maybe I need to just do the cells in use or occupied within the column. Yep, just tried it and that solved the issue for me. Thanks for waking me up with the trigger to the solution.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
skyport,

Sample raw data (with the selection range A1:A4):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">this is a test</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">not a test</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">another test</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">test of tests</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the macro (without any looping):

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">This Is A Test</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Not A Test</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Another Test</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Test Of Tests</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Sub ConvertSelectionUpper()
' hiker95, 08/22/2014, ME800718
With Selection
  .Value = Evaluate("=if(len(" & .Address & "),substitute(proper(" & .Address & "),"" And "","" and ""),"""")")
  .Columns.AutoFit
End With
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then select the range of cells, and, run the ConvertSelectionUpper macro.
 

skyport

Active Member
Joined
Aug 3, 2014
Messages
374

ADVERTISEMENT

Hiker, Thanks for everything. The program works perfect and does the job.
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
skyport,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 

skyport

Active Member
Joined
Aug 3, 2014
Messages
374

ADVERTISEMENT

Maybe you can help with one last question: How can I take a column of dates (column E) that continues to have more dates added and then have the last cell be filled with red ONLY if the value of that last cell is less than the value in cell H1 otherwise, it would stay the way I entered it
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
skyport,

I would suggest that you start a completely NEW thread, with an appropriate NEW title.

And, supply a screenshot of before, and, after.


In your NEW thread include:

post a screenshot of the actual raw data worksheet?

post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
1. MrExcel HTMLMaker20101230
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Installation instructions here:
http://www.mrexcel.com/forum/board-announcements/515787-forum-posting-guidelines.html#post2545970

2. Excel Jeanie
Download


If you are not able to give us screenshots:
You can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


Then send me a Private Message with a link to your NEW thread, and, I will have a look.
 

skyport

Active Member
Joined
Aug 3, 2014
Messages
374
Hi Hiker, ran into a very small snag. The only issue that comes up now that I have been using the program is it also caps letters after an apostrophe. Example: physician's comes out as Physician'S instead of Physician's. Any way to solve that one little issue?
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
skyport,

New sample raw data:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">this is a test</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">not a test</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">another test</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">test of tests</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">physician's</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

After the new macro:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">This Is A Test</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Not A Test</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Another Test</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #574123;background-color: #FFFFFF;;">Test Of Tests</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;">Physician's</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br />

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code:
Sub ConvertSelectionUpper_V2()
' hiker95, 08/26/2014, ME800718
Dim c As Range, p As Long, h As String, eh As String
Application.ScreenUpdating = False
With Selection
  .Value = Evaluate("=if(len(" & .Address & "),substitute(proper(" & .Address & "),"" And "","" and ""),"""")")
  .Columns.AutoFit
End With
For Each c In Selection
  If InStr(c, "'") Then
    h = c
    p = WorksheetFunction.Find("'", c, 1)
    eh = Right(h, Len(h) - p)
    h = Left(c, p) & LCase(eh)
    c = h
  End If
Next c
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ConvertSelectionUpper_V2 macro.
 

Forum statistics

Threads
1,136,442
Messages
5,675,892
Members
419,591
Latest member
mersanko

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top