Since the columns are sorted, how about a strategy like this:

1. compare the entered name with the last name in col A
2. if the result of step 1 is less then you found your col, put the name in
col A and sort that col
3. else compare the entered name with the last name in col D
4. and so on

"David Turner" wrote in message
...
Using XL2K, I have a sheet that has names (lastname, firstname) already
alphabetized in Cols A, D & G and a VBA routine that now asks the user
what column they want to add a name to, has the user supply a new name,
then adds that name to that column and sorts that range. I would like to
eliminate the first step and have the code add the name to the range with
the best alphabetical fit.

I would need some code to return that column letter since my routine also
has some conditions based on that column letter:

Sub Add_Member()
Dim New_Member As String, Col As String, rng As Range
Col = UCase(InputBox("Which Column, A or D or G?" & vbLf & vbLf _
& "Hint: Choose column where name" & vbLf & _
" will fit best alphabetically", , "A"))
If Col = "" Then Exit Sub
If Not (Col = "A" Or Col = "D" Or Col = "G") Then
MsgBox "Invalid entry": Exit Sub
End If
If Range(Cells(6, Col), Cells(6, Col)) = "" Then
MsgBox "Enter name manually": Exit Sub
End If
Set rng = Range(Cells(5, Col), Cells(5, Col).End(xlDown).Offset(1, 0))
If Col = "A" Then Maxed = rng(rng.Rows.Count).Offset(1, 3).Value
If Col = "D" Then Maxed = rng(rng.Rows.Count).Offset(1, 0).Value
If Col = "G" Then Maxed = rng(rng.Rows.Count).Offset(1, -3).Value
If Maxed = "Number in Educational Track" Then
MsgBox "This Column Full!!": Exit Sub
End If
New_Member = UCase(InputBox("Type New Member's Name" & _
vbNewLine & vbNewLine & "Like This: LastName, FirstName"))
If New_Member = "" Then Exit Sub
Application.ScreenUpdating = False
rng(rng.Rows.Count) = New_Member
rng.Resize(, 3).Sort Key1:=rng, Order1:=xlAscending
Application.ScreenUpdating = True
End Sub

--
David

kiat wrote

Since the columns are sorted, how about a strategy like this:

1. compare the entered name with the last name in col A
2. if the result of step 1 is less then you found your col, put the
name in col A and sort that col
3. else compare the entered name with the last name in col D
4. and so on

Sounds like a good strategy, only I don't know how to compare them with
code, particularly if the new last name begins with a letter that may not
be in any of the 3 existing lists.

--
David