PDA

View Full Version : Solved: Recocnise ascending numbers with text



Sir Babydum GBE
05-18-2006, 06:16 AM
Application.Goto Reference:="Curric_NameSort"
Selection.Sort Key1:=Range("B4"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


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
05-18-2006, 06:49 AM
Hi BD,

Why not add a custom sort list? 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:=xlTopToBottomIf 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: With Range("Curric_NameSort")
.Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=SortNum + 1, MatchCase:=False, Orientation:=xlTopToBottom
End WithMatt

Sir Babydum GBE
05-18-2006, 07:02 AM
Hi BD,

Why not add a custom sort list? 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:=xlTopToBottomIf 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: With Range("Curric_NameSort")
.Sort Key1:=.Columns(2), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=SortNum + 1, MatchCase:=False, Orientation:=xlTopToBottom
End WithMatt

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
05-18-2006, 07:12 AM
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
05-18-2006, 07:30 AM
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
05-19-2006, 09:35 AM
Hi Sir BD.
I see this is solved, but did you consider applying a custom format to your cells? "N"0
YHAOS
MD

mvidas
05-19-2006, 10:04 AM
YHAOSYour Humble And Obedient Servant?
Had to look that one up, I like that

Sir Babydum GBE
05-19-2006, 10:16 AM
Hi Sir BD.
I see this is solved, but did you consider applying a custom format to your cells? "N"0
YHAOS
MDYou 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
05-19-2006, 10:43 AM
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
05-19-2006, 10:52 AM
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 notAh! 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.