2 Collection questions

[Stuart]

The following code runs, but the Collection "VatCodes" holds no values
on completion:

With ws
FirstRow = .Range("M2").Row
LastRow = .Range("M65536").End(xlUp).Row
If LastRow = 2 Then GoTo Line2
.Unprotect
.Select

Set DataRange = .Range("M" & FirstRow, "M" & LastRow)
'use OERN otherwise an error will be thrown for duplicate entries
'in the Collection
On Error Resume Next
For Each R In DataRange
If Not IsEmpty(R) Then
VatCodes.Add R.Value
End If
Next
End If
End With

Q1: Why has the Collection failed to build please?

The following code will sort a Collection numerically:

For i = 1 To VatCodes.Count - 1
For j = i + 1 To VatCodes.Count
If VatCodes(i) > VatCodes(j) Then
Swap1 = VatCodes(i)
Swap2 = VatCodes(j)
VatCodes.Add Swap1, Before:=j
VatCodes(wsCtr).Add Swap2, Before:=i
VatCodes.Remove i + 1
VatCodes.Remove j + 1
End If
Next j
Next i

Q2: How can I sort a Collection alphabetically please?

Regards.

 

[Bob Phillips]

Q1: Why has the Collection failed to build please?

Is it failing because it exits because LastRow = 2? The code you posted has
a runtime error, an End If without an If

Q2: How can I sort a Collection alphabetically please?

Check out http://j-walk.com/ss/excel/tips/tip47.htm

 

[Tom Ogilvy]

Here is the way John Walkenbach originally wrote it:

' Sort the collection (optional)
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

does he have a

NoDupes(wsctr).Add Swap2, before:=i

I think not.

the sort algorithm he shows should handle either numbers or text.

When he adds to the collection doesn't he have two arguments:

For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)
' Note: the 2nd argument (key) for the Add method must be a string
Next Cell

You don't.

However, that wouldn't cause your collection to be empty - it would only
allow dupes.

Go back to the source and compare his code to yours

http://j-walk.com/ss/excel/tips/tip47.htm

how do you know the collection has failed to build? Maybe you are screwing
it up in your sort routine or because you have put in on error resume next
(and don't show resetting it), you are getting an error with the
NoDupes(wsctr).Add Swap2, before:=i line and can't see it so it looks like
nothing happens. You have a proven track record of this type of error
handling, so let us hope not.

 

[Stuart]

Many thanks to you both, and for the reminder to where
the code originated.
On Error GoTo 0 is in my routine, but missed from the
post.

Regards.

 

[Tom Ogilvy]

But you have to admit that this has bitten you many times. <g>

 

[Stuart]

No doubt about it!

Comparing the modified code with J Walk's showed I'd
also Dimmed it as a Collection, not a New Collection.
It now seems to work.

Thanks for the help.

Regards.