R
Rick S.
I got this code from this NG, I do not remember the authors name, but thanks
any how because it works quite well.
I am failing to find a way to make this macro ignore all characters within
brackets "()".
Any help is be appreciated.
'======
'Change Text to Upper Case or Proper Case. See Also:
'Force Upper Case/Proper Case
'Excel has 2 built in functions for converting text to
'either UPPER CASE or Proper Case. The 2 functions that
'do this are shown below;
'=UPPER(A1)
'=PROPER(A1)
'These Excel functions work well when referring to cells
'that house the text. However, there are many instances
'when using the Worksheet Function approach is not practical.
'The Excel macro code below can be used to change existing
'text to either UPPER CASE or Proper Case. If you run the
'macro with only a single cell selected it will work on the
'entire Worksheet. If you run the macro with more than 1
'cell selected it will work on only your selection.
'The other settings that the StrConv Function take are
'shown below. See the Excel VBA help for specifics.
Sub ConvertCase()
Dim rAcells As Range, rLoopCells As Range
Dim lReply As Long
'Set variable to needed cells
If Selection.Cells.Count = 1 Then
Set rAcells = ActiveSheet.UsedRange
Else
Set rAcells = Selection
End If
On Error Resume Next 'In case of NO text constants.
'Set variable to all text constants
Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
If rAcells Is Nothing Then
MsgBox "Could not find any text."
On Error GoTo 0
Exit Sub
End If
lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _
vbYesNoCancel, "OzGrid.com")
If lReply = vbCancel Then Exit Sub
If lReply = vbYes Then ' Convert to Upper Case
For Each rLoopCells In rAcells
rLoopCells = StrConv(rLoopCells, vbUpperCase)
Next rLoopCells
Else ' Convert to Proper Case
For Each rLoopCells In rAcells
rLoopCells = StrConv(rLoopCells, vbProperCase)
Next rLoopCells
End If
End Sub
'======
--
Regards
VBA.Newb.Confused
XP Pro
Office 2007
any how because it works quite well.
I am failing to find a way to make this macro ignore all characters within
brackets "()".
Any help is be appreciated.
'======
'Change Text to Upper Case or Proper Case. See Also:
'Force Upper Case/Proper Case
'Excel has 2 built in functions for converting text to
'either UPPER CASE or Proper Case. The 2 functions that
'do this are shown below;
'=UPPER(A1)
'=PROPER(A1)
'These Excel functions work well when referring to cells
'that house the text. However, there are many instances
'when using the Worksheet Function approach is not practical.
'The Excel macro code below can be used to change existing
'text to either UPPER CASE or Proper Case. If you run the
'macro with only a single cell selected it will work on the
'entire Worksheet. If you run the macro with more than 1
'cell selected it will work on only your selection.
'The other settings that the StrConv Function take are
'shown below. See the Excel VBA help for specifics.
Sub ConvertCase()
Dim rAcells As Range, rLoopCells As Range
Dim lReply As Long
'Set variable to needed cells
If Selection.Cells.Count = 1 Then
Set rAcells = ActiveSheet.UsedRange
Else
Set rAcells = Selection
End If
On Error Resume Next 'In case of NO text constants.
'Set variable to all text constants
Set rAcells = rAcells.SpecialCells(xlCellTypeConstants, xlTextValues)
If rAcells Is Nothing Then
MsgBox "Could not find any text."
On Error GoTo 0
Exit Sub
End If
lReply = MsgBox("Select 'Yes' for UPPER CASE or 'No' for Proper Case.", _
vbYesNoCancel, "OzGrid.com")
If lReply = vbCancel Then Exit Sub
If lReply = vbYes Then ' Convert to Upper Case
For Each rLoopCells In rAcells
rLoopCells = StrConv(rLoopCells, vbUpperCase)
Next rLoopCells
Else ' Convert to Proper Case
For Each rLoopCells In rAcells
rLoopCells = StrConv(rLoopCells, vbProperCase)
Next rLoopCells
End If
End Sub
'======
--
Regards
VBA.Newb.Confused
XP Pro
Office 2007