Excel VBA

css navigation by Css3Menu.com

Change Case to Mixed

My data comes from the mainframe in all UPPER case. We find it easier to read in Mixed or Proper case.

There is a routine that adds a space after MC and changes an O' to O’. The first makes the name look correct and the second is just my housekeeping. If the space is not added, MCCULLOUGH becomes Mccullough; with this Mc Cullough. I would like to fix the DEs but there is no hard fast rule.

Sub ChangeCase()
    Dim MemberCell As Range	'Housekeeping to establish variables
    Dim BottomRow As Integer
    Dim k As Integer
    
    Windows(ThisNewSheet).Activate
    Application.StatusBar = "Changing case..."	'Status Bar
    Range("A2").Select
        BottomRow = ActiveCell.SpecialCells(xlLastCell).Row 'Where to look
        Cells.Replace What:="MC", Replacement:="MC ", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
        Cells.Replace What:="'", Replacement:="’", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False
		'Clean up MC and O' before changing Case
            For k = 2 To BottomRow
                Cells(k, 1).Value = Application.Proper(Cells(k, 1).Value)
            Next
    Range("A2").Select
        '----- reserve for putting back special cases
    ActiveCell.Offset(1, 0).Select
End Sub


© 1999-2024

Updated:  01/23/2024 13:34
This page added:  01 April 1999