Userform

[Guest]

Dear all,

Please advise what's wrong with the code below to remove duplicates from a
list to a listbox in an userform. A warning dialog box stated "variable not
defined" to a word "Item"

Sub RemoveDuplicates1()
Dim AllCells As Range, Cell As Range
Dim NoDupes As New Collection

On Error Resume Next
For Each Cell In Range("A1:A54")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
On Error GoTo 0

For Each Item In NoDupes
UserForm1.ListBox1.AddItem Item
Next Item

UserForm1.Show
Unload UserForm1
End Sub

Thanks.

 

[Andy Pope]

Hi,

You have Option Explicit set at the top of your code module, which is
good. This requires you to declare ALL variables.

You have not declare the variable Item.

Cheers
Andy

 

[Bob Phillips]

Item is not a good word for a variable either. I haven't tested if it is a
problem, but as it is part of VBA syntax it should be avoided.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Andy Pope]

Hi Bob,

Yes your right, it is a bad choice but it does work.

Cheers
Andy

 

[Bob Phillips]

Many do Andy, but being part of the syntax means that we cannot guarantee
they always will.

Bob