title: check for duplicates in diffrent worksheets and thereafter print value
body: <p>I have searched but i cant seem to figure out how to print specified value in the column next to where i find my duplicate. What i have since earlier are code that first specify the diffrent ranges and thereafter look if a duplicate is found in sheet Y from sheet X. Sheet Le is this weeks information and sheet Be is the last weeks information. </p>
  
  <p>IF i find a duplicate in the specified range i want to on my Delivery sheet print in column A next to the duplicate either Delivered or not delivered depending on if my output from function compareAEO print true or false. </p>
  
  <p>The conditions that i am looking for are that if the we can find the same value that are in column B in sheet (Le) on sheet (Be) it will then check if the text in column F has changed. IF SO then it shall print in column A on sheet (Le) = Delivered. Otherwise not delivered. </p>
  
  <p>It then checks to se if the dates in column M is the same. IF not then it shall print Replanned in column A on sheet (Le).</p>
  
  <p>Shortly 
  IF value in cell on column B, Sheet (Le) = Value in column B, Sheet (Be) then
  value in column A on sheet Le = "Delivered" Else "not deliverd". </p>
  
  <p>Then</p>
  
  <p>If value in cell in column M, Sheet (Le) &lt;> If value in cell in column M, Sheet (Be) then value in column A, Sheet(Le) = "replanned"</p>
  
  <p>This is how my data looks like, 
  Sheet (Le)</p>
  
  <pre><code>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
  </code></pre>
  
  <p>For sheet (Be) my data looks like this</p>
  
  <pre><code>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
  </code></pre>
  
  <p>After the code has done its course i would like it to show for example,</p>
  
  <p>Sheet (Le)</p>
  
  <pre><code>    col A           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
  </code></pre>
  
  <p>Bascilly my Not delivered, delivered and Replanned statements does not work and my brain does not work. </p>
  
  <p>Can SO help save my day?</p>
  
  <pre><code>Sub checkASMT()
  Dim rng1 As Range
  Dim rng2 As Range
  Dim lastRowSource As Long
  Dim lastRowTarget As Long
  Dim row As Long
  Dim ASMT As String
  
  'Looping trough Range
  With ThisWorkbook.Worksheets("Le")
  
     lastRowTarget = .Range("B" &amp; .Rows.Count).End(xlUp).row
     For i = 29 To lastRowTarget
          ASMT = .Range("b" &amp; i).value
          'Define range and see if we can find duplicates
          With ThisWorkbook.Worksheets("Be")
              lastRowSource = .Range("B" &amp; .Rows.Count).End(xlUp).row
              Set rng1 = .Range("B3", "B" &amp; lastRowSource)
  
              row = findValueInRangeReturnRow(rng1, ASMT)
  
  
              'Check FAX 
              If compareAEO(i, row, "FAX") = True Then
                  '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 are correct
                  If compareAEO(i, row, "DAX") = False Then
                  .Worksheets("Le").ASMT.Offset(0, 1).value = "Replan"
  
  
              End If
  
  
  
          End With
      Next i
  
  End With
  
  
   End Sub
  </code></pre>
  
  <p>here are my first function</p>
  
  <pre><code> Function findValueInRangeReturnRow(rng As Range, value As Variant) As Long
  
          Set c = rng.Find(value, LookIn:=xlValues)
  
          If Not c Is Nothing Then
  
              findValueInRangeReturnRow = c.row
  
          End If
  
  End Function
  </code></pre>
  
  <p>My second function that checks if duplicates are found in specified ranges. </p>
  
  <pre><code>Function compareAEO(rad1 As Variant, rad2 As Variant, typeCOMPARE As String) As Boolean
  
  Dim col1 As String
  Dim col2 As String
  
    Select Case typeCOMPARE
      Case "FAX"
          col1 = "F"
          col2 = "F"
  
      Case "DAX"
          col1 = "M"
          col2 = "M"
  
      End Select
  
     If ThisWorkbook.Worksheets("Le").Range(col1 &amp; rad1).value =   ThisWorkbook.Worksheets("Be").Range(col2 &amp; rad2).value Then
  
      compareAEO = True
      Else
  
      compareAEO = False
  End If
  
  
  
  End Function
  </code></pre>