Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 94
  • Last Modified:

Change all uppercase words to capitalize the first letter only.

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
frugalmule
Asked:
frugalmule
  • 7
  • 6
  • 2
  • +2
1 Solution
 
Rgonzo1971Commented:
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
 
Naresh PatelTraderCommented:
Step 1 Copy data and past to attached Workbook - Sheet Actual file
Step 2 press ctrl+r
Done


Thanks
Proper.xlsm
0
 
Naresh PatelTraderCommented:
opps sorry i was attaching file..
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
frugalmuleAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Roy CoxGroup Finance ManagerCommented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
frugalmuleAuthor Commented:
Hi Roy,

I like the idea of not having a loop but I still get type mismatch
0
 
frugalmuleAuthor Commented:
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
 
Roy CoxGroup Finance ManagerCommented:
Attach an example of the data. My test workbook runs fine and  will be faster than a Loop
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
frugalmuleAuthor Commented:
Hi Roy,

I'll work on the example.  This is a video of the error.
http://screencast.com/t/yKBIBRDyMgPw
0
 
frugalmuleAuthor Commented:
Roy here is an sample that produces the error along with a video of the error.
http://screencast.com/t/yKBIBRDyMgPw
eesample.xlsb
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Please find the attached. I tested the code on Col. B and it worked without an issue.
eesample.xlsb
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Here is the a short demo...

http://www.screencast.com/t/cHqLeMcLryI4
0
 
frugalmuleAuthor Commented:
Yours works but is VERY VERY slow.  Hoping to test code from Roy and have requested a re-open.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now