Solved: Recocnise ascending numbers with text

[Sir Babydum GBE]

[vba]Application.Goto Reference:="Curric_NameSort"
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
[/vba]

Hello my lovely friends.

I had a cunning plan with the above macro (which is part os a bigger macro) which has failed to work and caused me to wonder whether life is worth it, or whether I should just throw in the towel and cease helping third world countries with my great advice and solutions.

The column that the above code sorts contains an "N" followed by a number. More and more "N"s are added to that list. When it sorts, it treats the whole thing as text and sorts it: N1, N10, N11, N2, N3 etc.

Can vba learn to ignore the "N" and sort by the number added to it - so that I get the desired N1, N2, N3 ... N9, N10, N11 affect?

I know I can type N01 instead of N1 - but because of matters of National Security, I can't do that. I just can't - ok?

Thanks!

Sir BD

[mvidas]

Hi BD,

Why not add a custom sort list?[vba] Dim AnArray() As String, i As Long, SortNum As Long
ReDim AnArray(400)
For i = 0 To 400
AnArray(i) = "N" & CStr(i)
Next
Application.AddCustomList ListArray:=AnArray
SortNum = Application.GetCustomListNum(AnArray)
Application.Goto Reference:="Curric_NameSort"
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=SortNum + 1, MatchCase:=False, Orientation:=xlTopToBottom[/vba]If the list exists already, a new one won't be added, otherwise it is. The listnumber is zero-based using GetCustomListNum, but the .Sort method uses a one-based list for whatever reason, so I have to add 1 to SortNum.
Also, if I knew what range Curric_NameSort referred to, I'd recommend removing the application.goto part and just using:[vba] With Range("Curric_NameSort")
.Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=SortNum + 1, MatchCase:=False, Orientation:=xlTopToBottom
End With[/vba]Matt

[Sir Babydum GBE]

Hi BD,

Why not add a custom sort list?[vba] Dim AnArray() As String, i As Long, SortNum As Long
ReDim AnArray(400)
For i = 0 To 400
AnArray(i) = "N" & CStr(i)
Next
Application.AddCustomList ListArray:=AnArray
SortNum = Application.GetCustomListNum(AnArray)
Application.Goto Reference:="Curric_NameSort"
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=SortNum + 1, MatchCase:=False, Orientation:=xlTopToBottom[/vba]If the list exists already, a new one won't be added, otherwise it is. The listnumber is zero-based using GetCustomListNum, but the .Sort method uses a one-based list for whatever reason, so I have to add 1 to SortNum.
Also, if I knew what range Curric_NameSort referred to, I'd recommend removing the application.goto part and just using:[vba] With Range("Curric_NameSort")
.Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=SortNum + 1, MatchCase:=False, Orientation:=xlTopToBottom
End With[/vba]Matt

Hey Matt!

Works a treat! thanks. I created Curric_NameSort as a dynamic range, would this make a difference to your recommendation?. What difference would it make to take out the application.goto out of the code, by the way?

[mvidas]

Glad to help! I do want to warn you that there is a limit to the size of the custom list, I think its between 400 and 425 but I couldn't think of it exactly (so I just used 400).
There isn't anything wrong with using application.goto. I just like to avoid the macro selecting anything on the sheets as it makes it a tad slower. The fact that it is a dynamic range wouldn't make a difference, as long as the first column is fixed. If the first column in the range is A then you can use my with block above, if its B you would just have to change the 2 to a 1. If it could be A or B then you'd have to continue with the way you have it.

[Sir Babydum GBE]

Glad to help! I do want to warn you that there is a limit to the size of the custom list, I think its between 400 and 425 but I couldn't think of it exactly (so I just used 400).
There isn't anything wrong with using application.goto. I just like to avoid the macro selecting anything on the sheets as it makes it a tad slower. The fact that it is a dynamic range wouldn't make a difference, as long as the first column is fixed. If the first column in the range is A then you can use my with block above, if its B you would just have to change the 2 to a 1. If it could be A or B then you'd have to continue with the way you have it.

400 is plenty!

I changed it according to your recommendation- works fine. Great help,

Cheers

[mdmackillop]

Hi Sir BD.
I see this is solved, but did you consider applying a custom format to your cells? "N"0
YHAOS
MD

[mvidas]

YHAOS

Your Humble And Obedient Servant?
Had to look that one up, I like that

[Sir Babydum GBE]

Hi Sir BD.
I see this is solved, but did you consider applying a custom format to your cells? "N"0
YHAOS
MD

You mean on the Format Cells / Number / Custom format - and typing "N"0?

I tried that just now then did a sort - but it didn't sort properly. I'm using XL97 - would that have made a difference.

Thanks to Matt it's working great - but your suggestion is excellent - if only I could get it to work!

[mvidas]

He is saying you should apply that custom format, then just enter 1,2,3,etc into the cells. They'll still show as N1, N2, N3, but will sort by numerical value. Really depends if you have control over the setup or not

[Sir Babydum GBE]

He is saying you should apply that custom format, then just enter 1,2,3,etc into the cells. They'll still show as N1, N2, N3, but will sort by numerical value. Really depends if you have control over the setup or not

Ah! I see.

I do have control over the sheet - but I fear that this may have an impact on other formulae...

I gues I could use the ampersand in the formulas to get over that but. For now - I'll stick with what you've done, thanks very much.

Certainly worth remembering though.