Run my Propercase macro only IF....

[Tim Hanson]

I have discovered there is no way a proper propercase name macro there are just to many variations on peoples name.

But I would like to use my very simple propercase macro on cells in a column such that the name is all caps or it is all caps to the right of the 1st ","

I can get it if there is no "," i.e MARY STEWART >> Mary Stewart but not MARY STEWART, Ph.D ( I want Mary Stewart' Pd.D) it gives me >> MARY STEWART, Ph.D

If my "IF" statement worked the macro would give me Mary Stewart, Pd.D

I think "If UCase(x) = x Then" is not written correctly

I have tiried and tried to figure this out to no avail

Thanks

Sub ProperCase()
    Dim ws As Worksheet
    Dim myRange As Range, cell As Range
    Dim tmpString As String
    Dim MyString As Variant
    Dim I As Long
    Dim x As String

    '~~> Change this to the relevant worksheet
    Set ws = Sheets("Source")

    With ws
        Set myRange = .Range("D2", .Range("D" & .Rows.Count).End(xlUp))

        For Each cell In myRange
            If InStr(1, cell.Formula, ",") Then
                MyString = Split(cell.Formula, ",")
               
                x = LBound(MyString)
           
              If UCase(x) = x Then

                tmpString = Application.WorksheetFunction.Proper(MyString(0))

                For I = 1 To UBound(MyString)
               
                    tmpString = tmpString & "," & MyString(I)
                    Next I
                    cell.Formula = tmpString
               
                End If
              End If

            If UCase(cell.Formula) = cell.Formula Then
                'cell.Formula = StrConv(cell.Formula, vbProperCase)
                cell.Formula = Application.WorksheetFunction.Proper(cell.Formula)
          End If
         
        Next cell
    End With
End Sub

 

[NARAYANK991]

Hi Tim ,

Try this :

Sub ProperCase()
    Dim myRange As Range, cell As Range
    Dim tmpString As String , Convert_This as String
    Dim MyString As Variant
    Dim I As Long
    Dim x As String

    '~~> Change this to the relevant worksheet
    With Sheets("Source")
        Set myRange = .Range("D2", .Range("D" & .Rows.Count).End(xlUp))

        For Each cell In myRange
            tmpString = vbNullString
            If InStr(1, cell.Formula, ",") Then
                MyString = Split(cell.Formula, ",")
                Convert_This = MyString(0)
                For I = 1 To UBound(MyString)
                    tmpString = tmpString & MyString(I) & ","
                Next I
                tmpString = Left(tmpString, Len(tmpString) - 1)
            Else
                Convert_This = cell.Value
            End If
            cell.Value = Application.WorksheetFunction.Proper(Convert_This) & IIf(tmpString = vbNullString, tmpString, "," & tmpString)
          
        Next cell
    End With
End Sub

Narayan

 

[Tim Hanson]

Thanks Again Narayan!!!, I will study this to see what you did this.