Compare and add

A

Aaron

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
 
T

Tom Ogilvy

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top