type mismatch error
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 |
type mismatch error
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 |
type mismatch error
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 |
type mismatch error
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 |
type mismatch error
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 |
type mismatch error
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 |
type mismatch error
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 |
type mismatch error
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 - |
type mismatch error
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 |
All times are GMT +1. The time now is 03:16 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com