Changing Case

[VENKYS]

I need to convert text strings to proper case - ie first letter in each word in upper case, and all the remaining ones in lowercase. I can do the same with @Proper(A1) for single cell. Is there a way out to convert the whole range instead of converting one cell at a time.

Would a macro assist and how to go about.

Regards

 

[VoG]

Try selecting the range then running

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

 

[mirabeau]

or perhaps you might be interested in this

Sub makeproper()
Selection.Value = Application.Proper(Selection)
End Sub

First select the range you want to make proper, or give it a name, or whatever.

 

[Dazzawm]

Try this, select the range you want to change and it gives you the three options of lower, upper and proper case.

Sub test()
Dim myCase, rng As Range, r As Range
myCase = Application.InputBox("Enter" & vbLf & "1 for Upper Case" & vbLf & _
                    "2 for Lower Case" & vbLf & "3 for Proper Case", type:=1)
If (myCase = False) + (Not myCase Like "[1-3]") Then Exit Sub
On Error Resume Next
Set rng = Selection.SpecialCells(2)
On Error GoTo 0
If Not rng Is Nothing Then
    For Each r In rng
        r.Value = StrConv(r.Value, myCase)
    Next
End If
End Sub

 

[VENKYS]

vOG, Mirabeau, Dazwm - Thanks all for your assistance.