Userform
 

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.

Userform
 

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

Freshman wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Userform
 

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" wrote in message
...
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

Freshman wrote:
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, Microsoft MVP - Excel
http://www.andypope.info

Userform
 

Hi Bob,

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

Cheers
Andy

Bob Phillips wrote:
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.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Userform
 

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

Bob

"Andy Pope" wrote in message
...
Hi Bob,

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

Cheers
Andy

Bob Phillips wrote:
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.


--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info