Question

（EXCEL VBA）Create a new worksheet and name it as “Roster”.You have the class roster in the worksheet “Roster” and the full names of students are in column A, starting in Cell A3, with last name last and case insensitive (such as Ben ALT). Write a sub that counts the number of names in the list with last name ALT and then displays this count in an MsgBox that is titled “Number of ALTs” and has an Exclamation icon button. Note that there might be last names, such as DALT, which should not be counted.

Answer 1

The VBA code is given below.

Option Explicit

Sub CountLastNames()
    ' Declarre the variables
    Dim count As Integer
    Dim i As Integer
    Dim fullName As String
    Dim lastName As String
    Dim nameParts() As String
    
    'The names in the Excel sheet starts from Row 3.  So hard code it.
    i = 3
    
    'Check if the cell is blank.  If blank exit the loop.  Otherwise it will loop continuously.
    'Assuming that after all the names are entered without any blank names.
    Do While (Cells(i, 1) <> "")
        'Extract the full name from the cell.  remove the spaces from both the ends, if any.
        fullName = Trim(Cells(i, 1))
        'split the full name it parts based on space.  This will store each part of the names into an array.
        nameParts = Split(fullName, " ")
        'extract the last member in the array which will be last name.
        lastName = nameParts(UBound(nameParts))
        'Remove any extra spaces
        lastName = Trim(lastName)
        
        'Check if the last name is "alt"
        'This is a case insensitive search
        If (LCase(lastName) = "alt") Then
            'If it matches, increment the counter.
            count = count + 1
        End If
        
        i = i + 1
    Loop
    
    'Display the information with the Messagebox title and Exclamation icon.
    MsgBox count, vbExclamation, "Number of ALTs"
End Sub

The screenshots of the code and output are provided below.