2 Collection questions
 

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, Befo=j
VatCodes(wsCtr).Add Swap2, Befo=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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004

2 Collection questions
 

"Stuart" wrote in message
...

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

2 Collection questions
 

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, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

does he have a

NoDupes(wsctr).Add Swap2, befo=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, befo=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.

--
Regards,
Tom Ogilvy




"Stuart" wrote in message
...
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, Befo=j
VatCodes(wsCtr).Add Swap2, Befo=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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004

2 Collection questions
 

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" wrote in message
...
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, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

does he have a

NoDupes(wsctr).Add Swap2, befo=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, befo=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.

--
Regards,
Tom Ogilvy




"Stuart" wrote in message
...
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, Befo=j
VatCodes(wsCtr).Add Swap2, Befo=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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004

2 Collection questions
 

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

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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" wrote in message
...
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, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

does he have a

NoDupes(wsctr).Add Swap2, befo=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, befo=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.

--
Regards,
Tom Ogilvy




"Stuart" wrote in message
...
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, Befo=j
VatCodes(wsCtr).Add Swap2, Befo=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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004

2 Collection questions
 

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.

"Tom Ogilvy" wrote in message
...
But you have to admit that this has bitten you many times. <g

--
Regards,
Tom Ogilvy

"Stuart" wrote in message
...
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" wrote in message
...
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, befo=j
NoDupes.Add Swap2, befo=i
NoDupes.Remove i + 1
NoDupes.Remove j + 1
End If
Next j
Next i

does he have a

NoDupes(wsctr).Add Swap2, befo=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, befo=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.

--
Regards,
Tom Ogilvy




"Stuart" wrote in message
...
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, Befo=j
VatCodes(wsCtr).Add Swap2, Befo=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.



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004






---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.571 / Virus Database: 361 - Release Date: 26/01/2004