Sort order of a collection

[simon]

I have created a collection of values from my worksheet using John
Walkenback's cited NoDupes method as below:

Set Allcells _
= Intersect(Range("B:M"),
Range("B3,B6,B9,B12,B15,B18,B21,B24").EntireRow)

On Error Resume Next
For Each cell In Allcells
If cell.Value = "" Then
'do nothing
Else
NoDupes.Add cell.Value, CStr(cell.Value)
End If
Next cell

'Sort the collection
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
swap1 = NoDupes(i)
swap2 = NoDupes(j)
NoDupes.Add swap1, before:=j
NoDupes.Add swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

The collection is then copied into another worksheet. However the
collection is sorting the items in the list correctly, but not in
numerical order. For example I have the following:

DNA_1
DNA_2
DNA_3
DNA_10
DNA_11

the sort collection codes puts them in the following order
DNA_1
DNA_10
DNA_11
DNA_2
DNA_3

where I want them to be in numerical order. i.e. DNA_1,DNA_2,DNA_3,
etc.

Can anyone guide me on how I can achieve this please?

 

[kounoike]

if all data has a format like [**** & "_" & number], then i would change the
code below

If NoDupes(i) > NoDupes(j) Then
to
If Val(Split(NoDupes(i), "_")(1)) > Val(Split(NoDupes(j), "_")(1)) Then

keizi

 

[simon]

Keizi,
Thank you very much. This worked perfectly. Could you explain to me
what the reformatted line is doing?

Simon

if all data has a format like [**** & "_" & number], then i would change the
code below

If NoDupes(i) > NoDupes(j) Then
to
If Val(Split(NoDupes(i), "_")(1)) > Val(Split(NoDupes(j), "_")(1)) Then

keizi

I have created a collection of values from my worksheet using John
Walkenback's cited NoDupes method as below:

Set Allcells _
= Intersect(Range("B:M"),
Range("B3,B6,B9,B12,B15,B18,B21,B24").EntireRow)

On Error Resume Next
For Each cell In Allcells
If cell.Value = "" Then
'do nothing
Else
NoDupes.Add cell.Value, CStr(cell.Value)
End If
Next cell

'Sort the collection
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
swap1 = NoDupes(i)
swap2 = NoDupes(j)
NoDupes.Add swap1, before:=j
NoDupes.Add swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

The collection is then copied into another worksheet. However the
collection is sorting the items in the list correctly, but not in
numerical order. For example I have the following:

the sort collection codes puts them in the following order
DNA_1
DNA_10
DNA_11
DNA_2
DNA_3

where I want them to be in numerical order. i.e. DNA_1,DNA_2,DNA_3,
etc.

Can anyone guide me on how I can achieve this please?- Hide quoted text -

- Show quoted text -

 

[kounoike]

Hi simon

Say NoDupes(i) is "DNA_2" and NoDupes(j) is "DNA_10", then "If NoDupes(i) >
NoDupes(j) Then NoDupes(j)" means If "DNA_2" > "DNA_10" Then and this comes
TRUE, so "DNA_10" comes before "DNA_2" swapping two data.

Split function returns a zero-based, one-dimensional array containing a
specified number of substrings.(look up in VBA help for more details)
Split(NoDupes(i), "_") and Split(NoDupes(j), "_") returns array {"DNA",
"2"}, {"DNA", "10"}respectively, so Split(NoDupes(i), "_") (1) returns a
string of "2" and Split(NoDupes(j), "_")(1) returns a string of "10". For
compareing two data as number, i used Val function like
Val(Split(NoDupes(i), "_")(1)) to change string to number. this makes
"DNA_2" come before "DNA_10".

But this only works in the case that the string before "_" is the same in
all data because comparing two data only using the string after "_". if this
is not the case, you need to take other way.

keizi

Keizi,
Thank you very much. This worked perfectly. Could you explain to me
what the reformatted line is doing?

Simon

if all data has a format like [**** & "_" & number], then i would change
the
code below

If NoDupes(i) > NoDupes(j) Then
to
If Val(Split(NoDupes(i), "_")(1)) > Val(Split(NoDupes(j), "_")(1)) Then

keizi

I have created a collection of values from my worksheet using John
Walkenback's cited NoDupes method as below:

Set Allcells _
= Intersect(Range("B:M"),
Range("B3,B6,B9,B12,B15,B18,B21,B24").EntireRow)

On Error Resume Next
For Each cell In Allcells
If cell.Value = "" Then
'do nothing
Else
NoDupes.Add cell.Value, CStr(cell.Value)
End If
Next cell

'Sort the collection
For i = 1 To NoDupes.Count - 1
For j = i + 1 To NoDupes.Count
If NoDupes(i) > NoDupes(j) Then
swap1 = NoDupes(i)
swap2 = NoDupes(j)
NoDupes.Add swap1, before:=j
NoDupes.Add swap2, before:=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

The collection is then copied into another worksheet. However the
collection is sorting the items in the list correctly, but not in
numerical order. For example I have the following:

the sort collection codes puts them in the following order
DNA_1
DNA_10
DNA_11
DNA_2
DNA_3

where I want them to be in numerical order. i.e. DNA_1,DNA_2,DNA_3,
etc.

Can anyone guide me on how I can achieve this please?- Hide quoted
text -

- Show quoted text -