Text Case Conversion Procedure "Proper Case" for Excel

[chandansify]

Dear All,

I have created a Process to change the case of text in Excel.:thumb

But I am sure that there is some better way to perform this.:dunno

Looking for some better way to perform this task.:think:


Sub ChangeProperCase()
' Procedure : ChangeProperCase
' DateTime : 7/8/2005 12:09
' Author : Chandan Banga
' Purpose : Changing Text case to Proper Case for Single/Multiple Range Selections
Dim cRange() As String, cRangeA() As String, cRangeA1() As String 'Declaring Array Variables
Dim i As Integer, iStart As Integer, iStart1 As Integer 'Declaring Counter Variables
Dim iRowStart As Integer, iRowEnd As Integer, iColStart As Integer, iColEnd As Integer
cRange = Split(Selection.Address, ",") 'Split Range Addresses in case of Multiple Range Selection
For i = LBound(cRange) To UBound(cRange)
cRangeA = Split(cRange(i), ":") 'Spliting Range
cRangeA1 = Split(cRangeA(0), "$") 'Spliting Cell Address
iColStart = Asc(cRangeA1(1)) - 64 'Getting Start Column Number
If Not UBound(cRangeA1) > 1 Then Exit Sub
iRowStart = cRangeA1(2) 'Getting Start Row Number
If UBound(cRangeA) > 0 Then
cRangeA1 = Split(cRangeA(1), "$")
iColEnd = Asc(cRangeA1(1)) - 64 'Getting End Column Number
iRowEnd = cRangeA1(2) 'Getting End Row Number
Else
iColEnd = iColStart
iRowEnd = iRowStart
End If
For iStart = iRowStart To iRowEnd 'Looping from Start Row to End Row
For iStart1 = iColStart To iColEnd 'Looping From Start Column to End Column in a Particular Row
Cells(iStart, iStart1).Value = StrConv(Cells(iStart, iStart1).Value, vbProperCase) 'Converting Cell Value in to Proper Case
Next
Next
Next
End Sub

Bye...:)

[excelliot]

Dear All,

I have created a Process to change the case of text in Excel.:thumb

But I am sure that there is some better way to perform this.:dunno

Looking for some better way to perform this task.:think:


Sub ChangeProperCase()
' Procedure : ChangeProperCase
' DateTime : 7/8/2005 12:09
' Author : Chandan Banga
' Purpose : Changing Text case to Proper Case for Single/Multiple Range Selections
Dim cRange() As String, cRangeA() As String, cRangeA1() As String 'Declaring Array Variables
Dim i As Integer, iStart As Integer, iStart1 As Integer 'Declaring Counter Variables
Dim iRowStart As Integer, iRowEnd As Integer, iColStart As Integer, iColEnd As Integer
cRange = Split(Selection.Address, ",") 'Split Range Addresses in case of Multiple Range Selection
For i = LBound(cRange) To UBound(cRange)
cRangeA = Split(cRange(i), ":") 'Spliting Range
cRangeA1 = Split(cRangeA(0), "$") 'Spliting Cell Address
iColStart = Asc(cRangeA1(1)) - 64 'Getting Start Column Number
If Not UBound(cRangeA1) > 1 Then Exit Sub
iRowStart = cRangeA1(2) 'Getting Start Row Number
If UBound(cRangeA) > 0 Then
cRangeA1 = Split(cRangeA(1), "$")
iColEnd = Asc(cRangeA1(1)) - 64 'Getting End Column Number
iRowEnd = cRangeA1(2) 'Getting End Row Number
Else
iColEnd = iColStart
iRowEnd = iRowStart
End If
For iStart = iRowStart To iRowEnd 'Looping from Start Row to End Row
For iStart1 = iColStart To iColEnd 'Looping From Start Column to End Column in a Particular Row
Cells(iStart, iStart1).Value = StrConv(Cells(iStart, iStart1).Value, vbProperCase) 'Converting Cell Value in to Proper Case
Next
Next
Next
End Sub


Bye...:)

can u send me yr excel file with this macro to have clear idea:think:

[mdmackillop]

Hi Chandan,
Welcome to VBAX.
If you select your code and click on the VBA button, it formats your code as above, making it more readable.
Regards
MD

[Bob Phillips]

Sub ChangeProperCase()
' Procedure : ChangeProperCase
' DateTime : 7/8/2005 12:09
' Author : Chandan Banga
' Purpose : Changing Text case to Proper Case for Single/Multiple Range Selections
Dim cell As Range
For Each cell In Selection
cell.Value = StrConv(cell.Value, vbProperCase) 'Converting Cell Value in to Proper Case
Next cell
End Sub

[excelliot]

Sub ChangeProperCase()
' Procedure : ChangeProperCase
' DateTime : 7/8/2005 12:09
' Author : Chandan Banga
' Purpose : Changing Text case to Proper Case for Single/Multiple Range Selections
Dim cell As Range
For Each cell In Selection
cell.Value = StrConv(cell.Value, vbProperCase) 'Converting Cell Value in to Proper Case
Next cell
End Sub


This is really short one?:beerchug:

[chandansify]

Great. Thanks

[excelliot]

Great. Thanks

Hi

if yr problem is solved then mark thread as solved above.

see u then

take care:beerchug: