a better variation with Dave's idea:

Sub Add_Member()
Dim xRow As Long, xCol As Long
Dim strNewNm As String
Do
strNewNm = UCase(InputBox("Type New Member's Name" & _
vbNewLine & vbNewLine & "Like This: LastName, FirstName"))
If Len(strNewNm) Then
If StrComp("HZZZ", strNewNm, vbTextCompare) = 1 Then
xCol = 1
ElseIf StrComp("MZZZ", strNewNm, vbTextCompare) = 1 Then
xCol = 4
Else
xCol = 7
End If
xRow = Cells(5, xCol).End(xlDown).Row + 1
If xRow = 65537 Then xRow = 5
Cells(xRow, xCol) = strNewNm
Range(Cells(5, xCol), Cells(xRow, xCol + 3)).Sort Cells(5, xCol)
Else
Exit Do
End If
Loop
End Sub