Sorting and Items to a list....

[Nuyorican]

The following code adds a new item to a list but changes the structure of the list. The current list is (1 - John, 2 - Mary, 3 - Frank and so on, till 200. If I add an item to the list, the code sorts my list by all the 1's and then all the 2's and so on. I want to add 201 - Jim and have him at the bottom of the list, thus keeping the original ( 1 thru 201.) I hope this makes sense. Thank you.

If Application.WorksheetFunction _
.CountIf(rng, c.Value) Then
Exit Sub
Else
lRsp = MsgBox(strMsg, vbQuestion + vbYesNo, "Add New Item?")
If lRsp = vbYes Then
i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
ws.Cells(i, rng.Column).Value = c.Value
rng.Sort Key1:=ws.Cells(1, rng.Column), _
Order1:=xlAscending, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

 

[tonyyy]

Nuyorican,

One approach would be to use a helper column - extract the numeric portion of the list to that column then sort using that column.

Cheers,

tonyyy

 

[Peter_SSs]

Another approach would be to reconstruct the list with all the numbers formatted with the same number of digits, then your sort would work straight off.
eg
001 - John
002 - Mary
003 - Frank
.
.
.
201 - Jim

 

[Nuyorican]

Thanks Peter, that was it.

 

[Peter_SSs]

Thanks Peter, that was it.

Cheers.