coding to prevent repeated rows and add to listbox

[dan2010]

Hi,

i have been trying to solve this for quite some time but cant seem to get the solution.i have created a macro that will copy the items in sheet1 of which, the total cost will be within budget, for 5 continuous years and then paste them in sheet 2. the problem i am facing now is that users might delete some items from sheet 2 and want to add other items in. so i am trying to create a macro to compare column a and b of the 2 sheets and add the items, that are not in sheet 2, into listbox1 in userform1.please look at the coding in sheet 2 and advice me how to solve my problem. thank you

[dan2010]

any suggestions to point me in the correct direction will do, does not necessary have to be a complete solution. thanks

[dan2010]

anybody with any idea?

[snb]

Please Login or Register  to view this content.

[dan2010]

sorry for the late reply. i was not around com until now.

i tried using your code and it gives me run time error '424', object required.
For Each cl In sheets1.Columns(1).SpecialCells(2) is highlighted when debugging.

btw i do not understand your coding. do u mind explain what does cl mean ?i tried finding on the net but came up with no explanation. thanks alot

[snb]

Please Login or Register  to view this content.

Check
- whether the workbook has 2 sheets,
- whether sheets(1) contains data in column A.
- whether sheets(2) contains data in column B
cl is a variable that stands for a cell in this code.

If you want to know more, please provide us with an example file.

[dan2010]

[QUOTE
If you want to know more, please provide us with an example file.[/QUOTE]

i had attached an example file in my 1st post. please take a look.
and your code is not working for me.tried.but an error has occurred as stated above.thank you.

[dan2010]

i would like to add on some facts.

i am trying to get the coding to compare 1st 3 columns in sheet1 and sheet2.purpose is to prevent anything from sheet2 showing up in the list box(prevent duplicates).So if the entire row of sheet 1 is different from all the rows in sheet 2, then add the row ( column a,b,c) into listbox1 in userform1.after which proceed to the next row in sheet1 and compare with all the rows in sheet2. thank you for looking at my problem and helping

[dan2010]

i have managed to find out and solve the error '424.had to change sheets1 and 2 to sheet1 and 2, and change listbox1.list to userform1.listbox1.list.

however, another run time error '70' (permission denied) had pop up at the line

"userform1.listbox1.list = split(mid(c01,2),"|")

[snb]

I think you have used the listbox's rowsource property.
Empty that property.

[dan2010]

erm...it shows only the 1st column, and it shows items that are already in sheet2 :S