chandansify
07-07-2005, 11:48 PM
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...:)
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...:)