excel - check for duplicates in diffrent worksheets and thereafter print value -

i have searched cant seem figure out how print specified value in column next find duplicate. have since earlier code first specify diffrent ranges , thereafter if duplicate found in sheet y sheet x. sheet le weeks information , sheet last weeks information.

if find duplicate in specified range want on delivery sheet print in column next duplicate either delivered or not delivered depending on if output function compareaeo print true or false.

the conditions looking if can find same value in column b in sheet (le) on sheet (be) check if text in column f has changed. if shall print in column on sheet (le) = delivered. otherwise not delivered.

it checks se if dates in column m same. if not shall print replanned in column on sheet (le).

shortly if value in cell on column b, sheet (le) = value in column b, sheet (be) value in column on sheet le = "delivered" else "not deliverd".

then

if value in cell in column m, sheet (le) <> if value in cell in column m, sheet (be) value in column a, sheet(le) = "replanned"

this how data looks like, sheet (le)

col b   col f    col m pz2408  x13    2017-02-13 pz2345  x30    2017-02-23 pz2463  x45    2017-02-25 pz2513  x13    2017-02-10 pz2533  x70    2017-02-05 pz2561  x60    2017-02-20 

for sheet (be) data looks this

col b   col f    col m pz2408  x30    2017-02-13 pz2345  x30    2017-02-23 pz2463  x30    2017-02-25 pz2513  x13    2017-02-05 pz2533  x13    2017-02-10 pz2561  x60    2017-02-17 

after code has done course show example,

sheet (le)

    col           col b   col f    col m    delivered       pz2408   x13    2017-02-13 not delivered      pz2345   x30    2017-02-23    delivered       pz2463   x45    2017-02-25   replanned        pz2513   x13    2017-02-10    delivered       pz2533   x70    2017-02-05    replanned       pz2561   x60    2017-02-20 

bascilly not delivered, delivered , replanned statements not work , brain not work.

can save day?

sub checkasmt() dim rng1 range dim rng2 range dim lastrowsource long dim lastrowtarget long dim row long dim asmt string  'looping trough range thisworkbook.worksheets("le")     lastrowtarget = .range("b" & .rows.count).end(xlup).row    = 29 lastrowtarget         asmt = .range("b" & i).value         'define range , see if can find duplicates         thisworkbook.worksheets("be")             lastrowsource = .range("b" & .rows.count).end(xlup).row             set rng1 = .range("b3", "b" & lastrowsource)              row = findvalueinrangereturnrow(rng1, asmt)               'check fax              if compareaeo(i, row, "fax") = true                 'debug.print compareasmt(i, row, "fax")                 worksheets("le").cells(i, asmt).value = "not delivered"                  else                  .worksheets("le").cells(i, asmt).value = "delivered"                  'check if dax correct                 if compareaeo(i, row, "dax") = false                 .worksheets("le").asmt.offset(0, 1).value = "replan"               end if            end     next  end    end sub 

here first function

 function findvalueinrangereturnrow(rng range, value variant) long          set c = rng.find(value, lookin:=xlvalues)          if not c nothing              findvalueinrangereturnrow = c.row          end if  end function 

my second function checks if duplicates found in specified ranges.

function compareaeo(rad1 variant, rad2 variant, typecompare string) boolean  dim col1 string dim col2 string    select case typecompare     case "fax"         col1 = "f"         col2 = "f"      case "dax"         col1 = "m"         col2 = "m"      end select     if thisworkbook.worksheets("le").range(col1 & rad1).value =   thisworkbook.worksheets("be").range(col2 & rad2).value      compareaeo = true     else      compareaeo = false end if    end function 

you getting last row of both pages in each loop. necessary them once @ top, outside loop. same range setting. can see put them @ top, before loop.

i don't know using asmt for. looks trying use range in of coding instead of range("b" & i). used strings in "b" column of le compare "b" column of when tested it.

it works me. you'll have change suit needs. don't need functions, accomplished within subroutine.

sub checkasmt() dim rng1 range dim rng2 range dim lastrowle long dim lastrowbe long dim row long dim asmt string dim lews worksheet dim tmprng range      set lews = worksheets("le")     lastrowle = sheets("le").cells(activesheet.rows.count, "b").end(xlup).row     lastrowbe = sheets("be").cells(activesheet.rows.count, "b").end(xlup).row     set rng1 = sheets("be").range("b3", "b" & lastrowbe)      = 29 lastrowle         set tmprng = sheets("le").range("b" & i)         asmt = tmprng.value          set c = rng1.find(asmt, lookin:=xlvalues)         if not c nothing                 row = c.row                 if thisworkbook.worksheets("le").range("f" & i).value = thisworkbook.worksheets("be").range("f" & row).value '                    worksheets("le").cells(i, asmt).value = "not delivered" '                   did intend use asmt column number? '                   i'm going hard code column 27 purposes.  can change if need                     lews.cells(i, 27).value = "not delivered"   ' column 27 "aa"                 else                     lews.cells(i, 27).value = "delivered"                 end if                 if thisworkbook.worksheets("le").range("m" & i).value = thisworkbook.worksheets("be").range("m" & row).value '                    .worksheets("le").asmt.offset(0, 1).value = "replan" '                       again don't understand reference asmt.  string value - unless numeric value in string '                       i'm going assume intended "replan" go column c on row                 else                     lews.range("c" & i).value = "replan"                 end if          end if     next     end sub