First Capital Letter

Basic Exercise

Statement

Transcribe the names from column A to column B while making the first letter a capital letter.

Suggested Concepts:

Dim - Declares a variable.
Cells - Allows VBA to interact with cells in the worksheet.
For Next - Repeats a group of statements a specified number of times.
Len - Returns an integer containing the number of characters in a string.
Ucase - Returns a string or character containing the specified string converted to uppercase.
Left - Returns a string that contains a specified number of characters from the left side of a string.
Right - Returns a string that contains a specified number of characters from the right side of a string.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Divide the names into two parts (left and right). Apply the Ucase function only for the first letter (left part).

Note: In a future exercise (VBA Text to Columns) you will find that this exercise can be solved in a very simple way: StrConv("target_string", vbProperCase).

Solved Exercise

Exercise Solving

In this solution, we segregate the first letter of the name and we make it a capital letter. Subsequently combine it again with the rest of the name.

Start by declaring the variables and finding the last data entry in the worksheet

Dim LastRow As Long
Dim CellVal As String
Dim FirstLett As String
Dim NameLen As Integer
Dim EndName As String
Dim Name As String
Dim i As Integer

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Define the size of the name with the Len function. Determine the first letter with the Left function and make it a capital letter with the Ucase function. Subsequently combine the capital letter with the rest of the name

For i = 3 To LastRow
    CellVal = Cells(i, 1).Value
    NameLen = Len(CellVal)
    FirstLett = Left(CellVal, 1)
    FirstLett = UCase(FirstLett)
    EndName = Right(CellVal, NameLen - 1)
    Name = FirstLett & EndName
    Cells(i, 2).Value = Name
Next i

Consolidated Answer

    Sub Solution()

    Dim LastRow As Long
    Dim CellVal As String
    Dim FirstLett As String
    Dim NameLen As Integer
    Dim EndName As String
    Dim Name As String
    Dim i As Integer

    LastRow = Cells(Rows.Count, 1).End(xlUp).Row

    For i = 3 To LastRow
        CellVal = Cells(i, 1).Value
        NameLen = Len(CellVal)
        FirstLett = Left(CellVal, 1)
        FirstLett = UCase(FirstLett)
        EndName = Right(CellVal, NameLen - 1)
        Name = FirstLett & EndName
        Cells(i, 2).Value = Name
    Next i

    End Sub

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED