Cap everyword in each cell of a column - macro issue

[skyport]

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

 

[hippiehacker]

what range do you select before you run the macro?

 

[skyport]

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]

skyport,

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

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

After the macro (without any looping):

Excel 2007
	A
1	This Is A Test
2	Not A Test
3	Another Test
4	Test 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.

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]

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

 

[hiker95]

skyport,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[skyport]

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]

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]

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]

skyport,

New sample raw data:

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

After the new macro:

Excel 2007
	A
1	This Is A Test
2	Not A Test
3	Another Test
4	Test Of Tests
5	Physician'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).

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.