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.
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.
 
Upvote 0
skyport,

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


Excel 2007
A
1this is a test
2not a test
3another test
4test of tests
5
Sheet1


After the macro (without any looping):


Excel 2007
A
1This Is A Test
2Not A Test
3Another Test
4Test Of Tests
5
Sheet1


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.
 
Upvote 0
skyport,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
skyport,

New sample raw data:


Excel 2007
A
1this is a test
2not a test
3another test
4test of tests
5physician's
6
Sheet1


After the new macro:


Excel 2007
A
1This Is A Test
2Not A Test
3Another Test
4Test Of Tests
5Physician's
6
Sheet1


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.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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
Back
Top