Capitalize first letter of string with vba

[Dokat]

Hi,

I am trying capitalize first letter of each string and lower case rest between columns C and F. I am using below vba code however it's giving me run time error 91. Can anyone help me with this problem?

Sub ConvertUppercaseInRange()

Application.ScreenUpdating = False
Worksheets("StaPVM").Activate

            Dim rng As Range
            
            rng = Columns("c:f").Select

            For Each rng In ActiveSheet.UsedRange

            rng.Value = StrConv(rng.Value, vbProperCase)

            Next rng
            
Application.ScreenUpdating = True
End Sub

 

[Trebor76]

Hi Dokat,

Try this:

Option Explicit
Sub ConvertUppercaseInRange()

    Dim wsSrc As Worksheet
    Dim rngCell As Range
    Dim lngLastRow As Long

    Application.ScreenUpdating = False
    
    Set wsSrc = ThisWorkbook.Sheets("StaPVM")
    lngLastRow = wsSrc.Range("C:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    
    For Each rngCell In wsSrc.Range("C2:F" & lngLastRow)
        If Len(rngCell) > 0 And IsNumeric(rngCell) = False Then
            rngCell.Value = StrConv(rngCell.Value, vbProperCase)
        End If
    Next rngCell
            
    Application.ScreenUpdating = True
    
End Sub

Note if the text is the result of a formula it will be replaced with its proper case value.

Regards,

Robert

 

[6StringJazzer]

I see you have a solution but I'll tell you the problem with your original code. Select does not return a value. You should probably have used Set if you wanted to set rng to a range but Select would still be a problem; you would just remove it. Your assignment is invalid and I am sure that is where you are getting the error (always give the error message and the line of code when asking about an error). That is line is useless anyway because you immediately use rng as an iterator.

 

[Dokat]

Hi Dokat,

Try this:

Option Explicit
Sub ConvertUppercaseInRange()

    Dim wsSrc As Worksheet
    Dim rngCell As Range
    Dim lngLastRow As Long

    Application.ScreenUpdating = False
   
    Set wsSrc = ThisWorkbook.Sheets("StaPVM")
    lngLastRow = wsSrc.Range("C:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    For Each rngCell In wsSrc.Range("C2:F" & lngLastRow)
        If Len(rngCell) > 0 And IsNumeric(rngCell) = False Then
            rngCell.Value = StrConv(rngCell.Value, vbProperCase)
        End If
    Next rngCell
           
    Application.ScreenUpdating = True
   
End Sub

Note if the text is the result of a formula it will be replaced with its proper case value.

Regards,

Robert

Thank you it worked perfectly

 

[Dokat]

Hi Dokat,

Try this:

Option Explicit
Sub ConvertUppercaseInRange()

    Dim wsSrc As Worksheet
    Dim rngCell As Range
    Dim lngLastRow As Long

    Application.ScreenUpdating = False
   
    Set wsSrc = ThisWorkbook.Sheets("StaPVM")
    lngLastRow = wsSrc.Range("C:F").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
   
    For Each rngCell In wsSrc.Range("C2:F" & lngLastRow)
        If Len(rngCell) > 0 And IsNumeric(rngCell) = False Then
            rngCell.Value = StrConv(rngCell.Value, vbProperCase)
        End If
    Next rngCell
           
    Application.ScreenUpdating = True
   
End Sub

Note if the text is the result of a formula it will be replaced with its proper case value.

Regards,

Robert

Is there anyway to exclude a string from the range. For Ex: If there is a string ("DEAN FARMS") between column C:F do not change the upper or lower case of the string?

 

[Dokat]

I see you have a solution but I'll tell you the problem with your original code. Select does not return a value. You should probably have used Set if you wanted to set rng to a range but Select would still be a problem; you would just remove it. Your assignment is invalid and I am sure that is where you are getting the error (always give the error message and the line of code when asking about an error). That is line is useless anyway because you immediately use rng as an iterator.

Thanks for calling out the issue.

 

[bebo021999]

May be:

For Each rngCell In wsSrc.Range("C2:F" & lngLastRow)
        If Len(rngCell) > 0 And IsNumeric(rngCell) = False Then
             rngCell.Value = Replace(StrConv( rngCell.Value, vbProperCase), "Dean Farms", "DEAN FARMS", , , vbBinaryCompare = True)
        End If
    Next rngCell

 

[Dokat]

May be:

For Each rngCell In wsSrc.Range("C2:F" & lngLastRow)
        If Len(rngCell) > 0 And IsNumeric(rngCell) = False Then
             rngCell.Value = Replace(StrConv( rngCell.Value, vbProperCase), "Dean Farms", "DEAN FARMS", , , vbBinaryCompare = True)
        End If
    Next rngCell

Thank you!