S
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.
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.