excel - Compare 2 Sheets of Data (Macro) -

i trying macro takes 2 sheets workbook , compare 2 different columns of data.

1. find number in 'price variances'!d2:d999999 , try match 'finance all'!e2:e999999

2. if match, take corresponding data 'finance all'!g2:g999999 , paste corresponding rows in 'price variances'!u2:u999999.

clarification

i want @ value in cell in 'price variances', column 'd', row '2', see if there match in 'finance all' column 'e' (look through entire column match).

if there is, want paste data corresponding row of match 'finance all', column 'g' 'price variances', column 'u', row '2' (this same row of original cell searching match from).

this need process every row there after in 'price variances', column 'd'.

below have far - please tweak & correct necessary.

sub price_variation_finance_match()   dim comparerange variant, x variant, y variant   ' set comparerange equal range   ' compare selection.   set comparerange = range("'finance all'!e2:e999999")   ' note: if compare range located on workbook   ' or worksheet, use following syntax.   ' set comparerange = workbooks("daily pricing (5)"). _         '   worksheets("price variances", "finance all").range("e2:e999999")  ' loop through each cell in selection , compare  ' each cell in comparerange.  each x in selection      each y in comparerange          if x = y x.offset(0, 17) = x      next y  next x 

end sub
i believe problem lies within last 'x' in "if x = y x.offset(0, 17) = x"

below original macro

sub find_matches() dim comparerange variant, x variant, y variant ' set comparerange equal range ' compare selection. set comparerange = range("c1:c5") ' note: if compare range located on workbook ' or worksheet, use following syntax. ' set comparerange = workbooks("book2"). _ '   worksheets("sheet2").range("c1:c5") ' ' loop through each cell in selection , compare ' each cell in comparerange. each x in selection     each y in comparerange         if x = y x.offset(0, 1) = x     next y next x end sub 

your if statement going return original value of x. think instead, want

if x = y x.offset(0, 17) = y.offset(0, 2) 

this gives value found in y column 2 columns right of lookup.

note macro slow, since cycling through each cell in y, if found match. if want first 1 found, suggest chaning loop to

for each x in selection      each y in comparerange          if x = y             x.offset(0, 17) = y.offset(0, 2)             exit         end if      next y  next x 

or better yet, use vlookup, whole function nicely.