Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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


Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value

Next Value
UserForm3.Show
End Sub


Thanks!
Alex

You can't use "And" to combine ranges. Instead, use "Union".

Replace
Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
with
Set AllCells =
Application.Union(Sheet1.Range("H2:H1000"),Sheet5. Range("D2:D1000"))


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"Ewing25" wrote in message
...
Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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


Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value

Next Value
UserForm3.Show
End Sub


Thanks!
Alex

You can't do this:
Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
"Union" is usually the way to do it, but not across sheets. Anyway you don't
need to union them, try

For Each Cell In Sheet1.Range("H2:H1000")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell
For Each Cell In Sheet5.Range("D2:D1000")
NoDupes.Add Cell.Value, CStr(Cell.Value)
Next Cell

HTH. Best wishes Harald

"Ewing25" wrote in message
...
Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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


Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value

Next Value
UserForm3.Show
End Sub


Thanks!
Alex

the set statement will never work because you can't combine ranges from two
different sheets. Below is the correct format in you had two columns on the
same shet

Set AllCells = Union(Sheets("Sheet1").Range("H2:H1000"), _
Sheets("Sheet1").Range("D2:D1000"))


"Ewing25" wrote:

Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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


Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value

Next Value
UserForm3.Show
End Sub


Thanks!
Alex

Ok well heres what im trying to do.

I have a column in sheet1 named Trips and a column in sheet5 named trips i
want to take the data in both calumns and have it displayed in a listbox when
the button is selected.

Do you have any idea how i can do that?



"Joel" wrote:

the set statement will never work because you can't combine ranges from two
different sheets. Below is the correct format in you had two columns on the
same shet

Set AllCells = Union(Sheets("Sheet1").Range("H2:H1000"), _
Sheets("Sheet1").Range("D2:D1000"))


"Ewing25" wrote:

Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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


Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value

Next Value
UserForm3.Show
End Sub


Thanks!
Alex

its saying

Run time error 1004
method union of object _application failed

"Chip Pearson" wrote:

You can't use "And" to combine ranges. Instead, use "Union".

Replace
Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
with
Set AllCells =
Application.Union(Sheet1.Range("H2:H1000"),Sheet5. Range("D2:D1000"))


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"Ewing25" wrote in message
...
Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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


Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value

Next Value
UserForm3.Show
End Sub


Thanks!
Alex

I didn't notice your sheet references. All cells in a Range object must be
on the same sheet. A Range cannot span multiple sheets.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



"Ewing25" wrote in message
...
its saying

Run time error 1004
method union of object _application failed

"Chip Pearson" wrote:

You can't use "And" to combine ranges. Instead, use "Union".

Replace
Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
with
Set AllCells =
Application.Union(Sheet1.Range("H2:H1000"),Sheet5. Range("D2:D1000"))


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)





"Ewing25" wrote in message
...
Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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


Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value

Next Value
UserForm3.Show
End Sub


Thanks!
Alex

chip said:
A Range cannot span multiple sheets.

so, perhaps you can link the data in sheet 5 to another, not used, not
seen, not printed area of sheet 1 - like waaaaaaay off in column FA or
something. then the ranges would be on the same sheet..........
just an idea
susan


On Jun 17, 10:10*am, Ewing25
wrote:
Ok well heres what im trying to do.

I have a column in sheet1 named Trips and a column in sheet5 named trips i
want to take the data in both calumns and have it displayed in a listbox when
the button is selected.

Do you have any idea how i can do that?



"Joel" wrote:
the set statement will never work because you can't combine ranges from two
different sheets. *Below is the correct format in you had two columns on the
same shet

Set AllCells = Union(Sheets("Sheet1").Range("H2:H1000"), _
* *Sheets("Sheet1").Range("D2:D1000"))

"Ewing25" wrote:

Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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

* * Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
* * On Error Resume Next
* * For Each Cell In AllCells
* * * * NoDupes.Add Cell.Value, CStr(Cell.Value)

* * Next Cell
* * On Error GoTo 0
* * For Each Value In NoDupes
* * On Error Resume Next
* * * * UserForm3.ListBox1.AddItem Value

* * Next Value
* * UserForm3.Show
End Sub

Thanks!
Alex- Hide quoted text -

- Show quoted text -

Or just use Harald's suggestion.

Susan wrote:

chip said:
A Range cannot span multiple sheets.

so, perhaps you can link the data in sheet 5 to another, not used, not
seen, not printed area of sheet 1 - like waaaaaaay off in column FA or
something. then the ranges would be on the same sheet..........
just an idea
susan

On Jun 17, 10:10 am, Ewing25
wrote:
Ok well heres what im trying to do.

I have a column in sheet1 named Trips and a column in sheet5 named trips i
want to take the data in both calumns and have it displayed in a listbox when
the button is selected.

Do you have any idea how i can do that?



"Joel" wrote:
the set statement will never work because you can't combine ranges from two
different sheets. Below is the correct format in you had two columns on the
same shet

Set AllCells = Union(Sheets("Sheet1").Range("H2:H1000"), _
Sheets("Sheet1").Range("D2:D1000"))

"Ewing25" wrote:

Im getting a type mismatch error on this code and im not sure why.

Im getting the error on the "Set AllCells" line.

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

Set AllCells = Sheet1.Range("H2:H1000") And Sheet5.Range("D2:D1000")
On Error Resume Next
For Each Cell In AllCells
NoDupes.Add Cell.Value, CStr(Cell.Value)

Next Cell
On Error GoTo 0
For Each Value In NoDupes
On Error Resume Next
UserForm3.ListBox1.AddItem Value

Next Value
UserForm3.Show
End Sub

Thanks!
Alex- Hide quoted text -

- Show quoted text -

--

Dave Peterson