April 9, 2009

Excel VBA Script

This is a 14 line script (not including declarations) that will take a single column of first and last names and move the last name to an adjacent column:

Public Sub getLastName()

Dim i As Integer
Dim b As Integer
Dim sLetter As String
Dim sLNAme As String

  For i = 2 To 10741 '*Comment 1
    If ActiveSheet.Cells(i, 2).Value <> "" Then '*Comment 2
      sLNAme = ActiveSheet.Cells(i, 2).Value
      For b = -1 To Len(sLNAme) - 1
        sLetter = Mid(sLNAme, Len(sLNAme) - b, 1)
        If sLetter = " " Then
          ActiveSheet.Cells(i, 3).Value = Mid(sLNAme, Len(sLNAme) - b + 1, b) '*Comment 3
          ActiveSheet.Cells(i, 2).Value = Left(sLNAme, Len(sLNAme) - (b + 1)) '*Comment 4
          sLetter = ""
          Exit For
        End If
      Next
    End If
  Next
End Sub

Comment 1: type in your range here; this script will affect the first 10741 cells, not including a header cell
Comment 2: the “2” on this line means “B” column…just an excelism
Comment 3: the “3” on this line means “C” column in excel
Comment 4: again, “2” on this line means “B” column

•