Return Col for best alphabetical fit
 

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

Return Col for best alphabetical fit
 

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

Return Col for best alphabetical fit
 

Use StrComp:

Private Sub t()
Dim xC As Range, i As Long, rc As Long
Dim strNewNm As String
strNewNm = UCase(InputBox("Type New Member's Name" & _
vbNewLine & vbNewLine & "Like This: LastName, FirstName"))

For i = 1 To 7 Step 3
Set xC = Cells(1, i).End(xlDown)
rc = StrComp(xC.Text, strNewNm, vbTextCompare)
Select Case rc
Case 0 'str1=str2
MsgBox "duplicate name"
Case 1 'str1str2, this is where we want to put the new name
Exit For
Case -1 'str1<str2
'do nothing
Case Else 'problem
'handle error
End Select
Next
xC.Offset(1, 0) = strNewNm
xC.Resize(, 3).Sort Key1:=xC, Order1:=xlAscending
end sub

I did that real quick, so recheck the code.

"David Turner" wrote in message
...
kiat wrote

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


An additional circumstance: The three columns were initally set up with
names distributed evenly among them, so column D begins with names where
the last name begins with the same letter as those at the end of column A.
This may be true when comparing D to G.

I'm going to be distributing this workbook to three other counties where
their own name lists will vary and where the users are much less computer
savvy than I, and I don't want to have to try to explain "just be sure
you..." type things to them over the phone. They are already reluctant to
use the computer for anything, anyway.

--
David

Return Col for best alphabetical fit
 

I'm kind of confused on how the data is laid out. It looks like you're limiting
the numbers in each set of 3 columns A:C, D:F, and G:I.

If you add more names to what would have gone in column A, do you move some from
the bottom of A to the top of D and the from the bottom of D to the top of G?
And if you do, what are your limits per column (25 rows, 60 rows, 99 rows)?

Depending on your answers, I think I'd approach it differently.

I'd either keep the list as one giant column (set of 3 columns) and then just
add to the bottom of that list, sort that list and then regenerate the columns
into a nice layout (for printing???).

Alternatively, if I had to keep the columns separated, I'd still combine them
all into one, sort it and then redistribute the data (have the macro do it so it
was transparent to the user).

And no matter what approach you take, I'd do one more thing. I'd separate the
lastname and firstname into separate columns. It might make things
(mailmerge???) a lot easier later. But then you might have to go to groups of 4
columns per "set".



David Turner wrote:

kiat wrote

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


An additional circumstance: The three columns were initally set up with
names distributed evenly among them, so column D begins with names where
the last name begins with the same letter as those at the end of column A.
This may be true when comparing D to G.

I'm going to be distributing this workbook to three other counties where
their own name lists will vary and where the users are much less computer
savvy than I, and I don't want to have to try to explain "just be sure
you..." type things to them over the phone. They are already reluctant to
use the computer for anything, anyway.

--
David

--

Dave Peterson

Return Col for best alphabetical fit
 

kiat wrote

I did that real quick, so recheck the code.


Thanks. As written, it at least puts the name in the proper column. It
replaces the name at the top of the list, though, replacing the name there
and doesn't sort the results. Also, name to Column A goes 2 rows above
where it should be.

--
David

Return Col for best alphabetical fit
 

Dave Peterson wrote

I'm kind of confused on how the data is laid out. It looks like
you're limiting the numbers in each set of 3 columns A:C, D:F, and
G:I.

Initial number of names will vary per county, but they are advised to
start out with names distributed (i.e. A-H in A, I-M in D and N-Z in G)
evenly between columns A, D & G (adjacent columns, BC, EF and HI are for
X's for a couple of tracking categories).

Kiat's offering is real close, except the results don't sort, and I need
to add some error trapping like I had in my original routine for such
things as filled-up name ranges or ones containing less than 2 names.

If you add more names to what would have gone in column A, do you move
some from the bottom of A to the top of D and the from the bottom of D
to the top of G? And if you do, what are your limits per column (25
rows, 60 rows, 99 rows)?

Current layout prints nicely on a landscape single page and accommodates
up to 99 names (plenty of room for expanding enrollment for now). Each
NAME column will have some blank cells below for new names. Only if a
column fills up will it be necessary to redistribute names manually.

Alternatively, if I had to keep the columns separated, I'd still
combine them all into one, sort it and then redistribute the data
(have the macro do it so it was transparent to the user).

Therein lies the rub, how to do this via macro and preserve such things
as borders? Ranges would definitely have to be dynamic. Of course,
sorting a single column is easier (wishing Excel could sort all my
columns at once).


--
David

Return Col for best alphabetical fit
 

Dave or Kiat,

Ok, assuming this all works (see below) to ADD a name, and I don't worry
about error trapping for now, what changes can I make to REMOVE a name:

Sub Add_Member()
Dim xC As Range, i As Long, rc As Long, rng As Range
Dim strNewNm As String
strNewNm = UCase(InputBox("Type New Member's Name" & _
vbNewLine & vbNewLine & "Like This: LastName, FirstName"))
If strNewNm = "" Then Exit Sub
For i = 1 To 7 Step 3
Set xC = Cells(5, i).End(xlDown)
rc = StrComp(xC.Text, strNewNm, vbTextCompare)
Select Case rc
Case 1
Exit For
End Select
Next
Set rng = Range(Cells(5, i), Cells(5, i).End(xlDown).Offset(1, 0))
rng(rng.Rows.Count) = strNewNm
rng.Resize(, 3).Sort Key1:=rng, Order1:=xlAscending
End Sub

Sub Delete_Member
??????????
End Sub

--
David

Return Col for best alphabetical fit
 

kiat wrote

not the kinda coding my professor would approve, but if it works, well...


I'm open to new ideas, if you care to share. Your For...Next...Case idea
was very valuable in eliminating the need to specify the target column
ahead of time. I must admit, I don't understand the Case stuff and am
flying blind when implementing it.

BTW, I discovered later I had to put the sort line BEFORE Next i

I've also found in my morphed Add_Member routine that I can't add a name
that would fall alphabetically AFTER the last name in Col G. :(

--
David

Return Col for best alphabetical fit
 

Try this:

Sub Add_Member()
Dim xRow As Long, i 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
For i = 1 To 7 Step 3
xRow = Cells(5, i).End(xlDown).Row
If StrComp(Cells(xRow, i).Text, strNewNm, vbTextCompare) = 1 Or
i = 7 Then
xRow = xRow + 1
Cells(xRow, i) = strNewNm
Range(Cells(5, i), Cells(xRow, i + 3)).Sort Cells(5, i)
Exit For
End If
Next
Else
Exit Do
End If
Loop
End Sub

Warning, this code has serious limitation, it fovaors to put names in column
G. I think you should consider Dave Peterson's strategy and change the code
there to suit your needs.

"David Turner" wrote in message
...
kiat wrote

not the kinda coding my professor would approve, but if it works,
well...


I'm open to new ideas, if you care to share. Your For...Next...Case idea
was very valuable in eliminating the need to specify the target column
ahead of time. I must admit, I don't understand the Case stuff and am
flying blind when implementing it.

BTW, I discovered later I had to put the sort line BEFORE Next i

I've also found in my morphed Add_Member routine that I can't add a name
that would fall alphabetically AFTER the last name in Col G. :(

--
David

Return Col for best alphabetical fit
 

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

Return Col for best alphabetical fit
 

kiat wrote

a better variation with Dave's idea:


I tried both routines. Like the loop.

Dave's idea assumes all last names beginning with H are in column A and M
are in column D (I sorta hinted at that when he jumped in). That may not be
the case for all users.

And both routines result in blank cells being inserted to the right of the
new name in the name column immediately to the right of new name if the
name column to the right contains the same or fewer names as the target
column. IOW, if I add a name to column A and column D contains the same or
fewer names, column D winds up with empty cells to the right of the new
name in column A. Same for new names going into column D -- column G will
wind up with the empty cells to the right of the new name.

What would really be neat is if you could get your FIRST offering to
perform a sort as it looks like it should instead of just adding the name
to the bottom of the proper column.

--
David

Return Col for best alphabetical fit
 

kiat wrote

Try this:

I fumbled around until I got this:

Sub Add_Member()
Dim xC As Range, i As Long, rc As Long
Dim strNewNm As String
strNewNm = UCase(InputBox("Type New Member's Name" & _
vbNewLine & vbNewLine & "Like This: LastName, FirstName"))
For i = 1 To 7 Step 3
Set xC = Cells(5, i).End(xlDown)
rc = StrComp(xC.Text, strNewNm, vbTextCompare)
Select Case rc
Case 1
Exit For
End Select
Next
xC.Offset(1, 0) = strNewNm
If strNewNm xC.Offset(0, 0) Then Exit Sub
Range(Cells(5, i), Cells(5, i).End(xlDown)).Resize(, 3).Sort Key1:=xC, _
Order1:=xlAscending
End Sub

Works!!

--
David

Return Col for best alphabetical fit
 

Dave Peterson wrote

One more shot:


Now THAT is one nice piece of work!!!

Kudos to you and your efforts on my behalf.

--
David

Return Col for best alphabetical fit
 

Dave Peterson wrote

If ExistingNamesCounter = maxLen * 3 Then
MsgBox "Too many names"
End If


One minor point: Shouldn't this condition, if met, exit the Sub?

--
David

Return Col for best alphabetical fit
 

Yep. Sorry.


David Turner wrote:

Dave Peterson wrote

If ExistingNamesCounter = maxLen * 3 Then
MsgBox "Too many names"
End If


One minor point: Shouldn't this condition, if met, exit the Sub?

--
David

--

Dave Peterson