Results 1 to 4 of 4

Amend Upper Case macro to ignore brackets

Threaded View

  1. #1
    Forum Contributor Rick_Stanich's Avatar
    Join Date
    11-21-2005
    Location
    Ladson SC
    MS-Off Ver
    Office365
    Posts
    1,177

    Amend Upper Case macro to ignore brackets

    I got this macro from some NG some where (Thanks), what I need help on is making the macro ignore all characters in brackets "()".
    I am absolutely dying trying to mod this macro.
    Heck, is it even possible?

    '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
    As always, I value all help provided.

    To the MOD who improved my title, Thanks!
    Last edited by Rick_Stanich; 03-10-2008 at 06:43 PM.
    Regards

    Rick
    Win10, Office 365

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1