excel vba - How to compare columns for unique data between two workbooks -

i want compare data column d in fortest1.xlsx column f in tested.xlsm. if matched, nothing. if not matched, copy column c in fortest.xlsx column c e in tested.xlsm. may know how can achieve this? code running without error, however, doesn't generate result.

thanks in advanced.

sub test() 

'application.screenupdating = true dim wba workbook set wba = thisworkbook

dim wbb workbook set wbb = workbooks.open(filename:="c:\users\maggie\desktop\fortest1.xlsx")  dim sheeta worksheet dim sheetb worksheet set sheeta = wba.sheets("up") set sheetb = wbb.sheets("up")  dim erowa integer dim erowb integer erowa = sheeta.range("f" & rows.count).end(xlup).row 'last line data in workbook (activeworkbook) erowb = sheetb.range("d" & rows.count).end(xlup).row  'last line data in workbook b (opened workbook)  dim match boolean dim erow long dim i, j long dim r1, r2 range = 1 erowa     set r1 = sheeta.range("f" & i)     match = false     j = 1 erowb         set r2 = sheetb.range("d" & j)         if r1 = r2             match = true         end if         next j         if not match         erow = range("c" & rows.count).end(xlup).row + 1         sheetb.range("a" & j & ":c" & j).copy destination:=sheeta.range("c" & erow & ":e" & erow) end if next wbb.close (false) 

end sub enter image description here

i'm not quite sure understand want achieve, code has several problems.

proper indenting 1 of issues. not obvious @ first glance how code nested.

• when comparison not true, match variable not reset false, still shows true. fix: move match = false j loop, set false @ beginning of j loop
• the match variable set , overwritten 7 times in j loop before evaluated , done it.

the suggestions above applied code result in this:

for = 1 erowa     set r1 = sheeta.range("f" & i) '    match = false ' move j loop     j = 1 erowb         match = false         set r2 = sheetb.range("d" & j)         if r1 = r2             match = true         end if '        next j   ' loops , overwrites match variable         if not match             erow = range("c" & rows.count).end(xlup).row + 1             sheetb.range("a" & j & ":c" & j).copy destination:=sheeta.range("c" & erow & ":e" & erow)         end if     next j next 

the code runs through loop twice , in each loop runs through j loop 7 times. if not want achieve, pipe up.