Solved

Change all uppercase words to capitalize the first letter only.

Posted on 2016-07-29
18
69 Views
Last Modified: 2016-08-04
I have 39 columns and 13,282 rows in a spreadsheet.

I need to prompt for column selection and change all uppercase words in the selected column to capitalize only the first letter of each word.
0
Comment
Question by:frugalmule
  • 7
  • 6
  • 2
  • +2
18 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 41734839
Hi,

pls try

Sub Macro()

Set Rng = Application.InputBox("Select Column", "Column selection", , , , , , 8)
    For Each c In Rng.SpecialCells(xlCellTypeConstants, 2)
        c.Value = Application.Proper(c.Value)
    Next
End Sub

Open in new window

Regards
0
 
LVL 8

Expert Comment

by:itjockey
ID: 41734841
Step 1 Copy data and past to attached Workbook - Sheet Actual file
Step 2 press ctrl+r
Done


Thanks
Proper.xlsm
0
 
LVL 8

Expert Comment

by:itjockey
ID: 41734843
opps sorry i was attaching file..
0
 

Author Comment

by:frugalmule
ID: 41734851
I don't need the actual file, just the code.  When I run it on the sheet in question, then input column AC, it tells me type mismatch.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734887
You may try something like this...
The code below will prompt you to enter the column letter and it will convert all the strings in that column into proper strings.
Sub ConvertColumnToHaveProperStrings()
Dim colStr As String
Dim colNum As Long
Dim cell As Range
colStr = Application.InputBox("Input Column Letter...", "Column Letter Please!")
On Error Resume Next
colNum = Range(colStr & 1).Column
If colNum = 0 Then
   MsgBox "You have entered invalid column letter...", vbCritical, "Invalid Column Letter!"
   Exit Sub
End If
For Each cell In ActiveSheet.Columns(colNum).SpecialCells(xlCellTypeConstants, 2)
   cell.Value = StrConv(cell.Value, 3)
Next cell
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

0
 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41734890
No need for a Loop

Sub test()
    Dim rng As Range
    Set rng = Application.InputBox("Select Column", "Column selection", , , , , , 8)
    rng.SpecialCells(xlCellTypeConstants, 2).Value = Application.Proper(rng.Value)

End Sub

Open in new window

0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734894
That's correct Roy! Thanks for pointing this out. :)

In this case the code can be written as below.....

Sub ConvertColumnToHaveProperStrings()
Dim colStr As String
Dim colNum As Long
Dim cell As Range
colStr = Application.InputBox("Input Column Letter...", "Column Letter Please!")
On Error Resume Next
colNum = Range(colStr & 1).Column
If colNum = 0 Then
   MsgBox "You have entered invalid column letter...", vbCritical, "Invalid Column Letter!"
   Exit Sub
End If
ActiveSheet.Columns(colNum).SpecialCells(xlCellTypeConstants, 2).Value = StrConv(ActiveSheet.Columns(colNum).SpecialCells(xlCellTypeConstants, 2), 3)
MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window

0
 

Author Comment

by:frugalmule
ID: 41734901
Hi Roy,

I like the idea of not having a loop but I still get type mismatch
0
 

Author Comment

by:frugalmule
ID: 41734932
I also tested bottom solution from Subodh, and it allowed me to input a column letter but does not appear to have done anything.  I'm not 100% sure whether it just missed some or didn't get any of them.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:Roy_Cox
ID: 41734937
Attach an example of the data. My test workbook runs fine and  will be faster than a Loop
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734957
Okay try the older version in the attached and let me know if that works for you.
Click the button on the sheet to run the code on Module1.
StringsToProperCase.xlsm
0
 

Author Comment

by:frugalmule
ID: 41734959
Hi Roy,

I'll work on the example.  This is a video of the error.
http://screencast.com/t/yKBIBRDyMgPw
0
 

Author Comment

by:frugalmule
ID: 41734965
Roy here is an sample that produces the error along with a video of the error.
http://screencast.com/t/yKBIBRDyMgPw
eesample.xlsb
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734979
Please find the attached. I tested the code on Col. B and it worked without an issue.
eesample.xlsb
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41734983
If you want to run that code on any column, just input the column letter only.
e.g. if you want to run that code on column M, then input letter M in the inputbox.
0
 
LVL 28

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41735000
Here is the a short demo...

http://www.screencast.com/t/cHqLeMcLryI4
0
 

Author Comment

by:frugalmule
ID: 41735008
Yours works but is VERY VERY slow.  Hoping to test code from Roy and have requested a re-open.
0
 
LVL 28

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41735025
Okay replace my existing code with the following one. It doesn't involve loop.
Sub ConvertColumnToHaveProperStrings()
Dim colStr As String
Dim rng As Range
colStr = Application.InputBox("Input Column Letter...", "Column Letter Please!")
On Error Resume Next
colNum = Range(colStr & 1).Column
If colNum = 0 Then
   MsgBox "You have entered invalid column letter...", vbCritical, "Invalid Column Letter!"
   Exit Sub
End If
Set rng = Range(colStr & ":" & colStr)
Range(colStr & ":" & colStr).SpecialCells(xlCellTypeConstants, 2).Value = Application.Proper(rng.SpecialCells(xlCellTypeConstants, 2).Value)

MsgBox "Task Completed.", vbInformation, "Done!"
End Sub

Open in new window


OR you can replace the Roy's code with the following code...
Sub test()
Dim rng As Range
Set rng = Application.InputBox("Select Column", "Column selection", , , , , , 8)
rng.SpecialCells(xlCellTypeConstants, 2).Value = Application.Proper(rng.SpecialCells(xlCellTypeConstants, 2).Value)
End Sub

Open in new window

0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Modern/Metro styled message box and input box that directly can replace MsgBox() and InputBox()in Microsoft Access 2013 and later. Also included is a preconfigured error box to be used in error handling.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now