Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and add
I need some help on coding the following
I have File1 and File2. I need File1 to look and Column A on File2 and if there is Values there that is not in File1 Column A add it to the bottom of File1 ColumnA Example: File1 Item Values 1234 2345 23456 File2 Item Values 123 2345 23456 231 234 It would add 231 and 234 to File1.. I have the following code that I can not get to work. Can you help? Sub ABC() Dim sh1 as Worksheet, sh2 as Worksheet Dim lastrow as Long, i as Long Dim rng as Range, cell as Range set sh1 = Workbooks("File1.xls").Worksheets(1) set sh2 = Workbooks("File2.xls").Worksheets(1) lastrow = sh1.Cells(rows.count,1).End(xlup).row i = 1 set rng = sh2.Range(sh2.cells(1,1),sh2.cells(1,1).End(xldown )) for each cell in rng if application.countif(sh1.Columns(1),cell) = 0 then sh1.cells(lastrow + i,1) = cell i = i + 1 end if Next End sub Thanks, Aaron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare and add
That code ran perfectly for me and did exactly what you described you wanted
to do. I copied the code you posted and put it in a workbook. Copied your data as you show putting it in File1.xls and File2.xls in the first worksheet in each in column A.. Ran the code and it produced 1234 2345 23456 231 234 in File1.xls. Exactly as you said you wanted. I did it twice, once with the header Item Values in A1 and once with the data starting in A1 with no header. Each worked perfectly. I suspect you have reversed file1 and file2 with respect to the code. -- Regards, Tom Ogilvy "Aaron" wrote: I need some help on coding the following I have File1 and File2. I need File1 to look and Column A on File2 and if there is Values there that is not in File1 Column A add it to the bottom of File1 ColumnA Example: File1 Item Values 1234 2345 23456 File2 Item Values 123 2345 23456 231 234 It would add 231 and 234 to File1.. I have the following code that I can not get to work. Can you help? Sub ABC() Dim sh1 as Worksheet, sh2 as Worksheet Dim lastrow as Long, i as Long Dim rng as Range, cell as Range set sh1 = Workbooks("File1.xls").Worksheets(1) set sh2 = Workbooks("File2.xls").Worksheets(1) lastrow = sh1.Cells(rows.count,1).End(xlup).row i = 1 set rng = sh2.Range(sh2.cells(1,1),sh2.cells(1,1).End(xldown )) for each cell in rng if application.countif(sh1.Columns(1),cell) = 0 then sh1.cells(lastrow + i,1) = cell i = i + 1 end if Next End sub Thanks, Aaron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare and get the best value | Excel Worksheet Functions | |||
Compare | Excel Worksheet Functions | |||
How do I compare cells and if FALSE compare to next cell in EXCEL | Excel Worksheet Functions | |||
String compare doesn't compare? | Excel Programming | |||
compare data from one column with another and compare result to yet another | Excel Programming |