title: VBA to compare two sheets and replace the value in sheet 1
body: <p>I have two Sheets , sheet1 and sheet2. </p>
  
  <p>I am having 17 columns in sheet1 and 14 column in sheet2. </p>
  
  <p>I have ID in column L of sheet1( the id starts with D2B and 4). one ID is 11 to 13 Digits Long, while the other is 8 Digit Long. In the endresult, i Need only ID with D2B.</p>
  
  <p>In column L of sheet 2,I have ID only starting with 4. and it is 8digit Long. Also, I have Column A which Contains only D2B. </p>
  
  <p>I am comparing both column (L) from sheet 1 and shee2. if the Id is present in sheet1 , then i copy the result to column M of sheet2. Since, I Need only Id with D2B, I check if column L and M of sheet 2 is matching, If they are matching, then I copy the corresponding ID d2B from column A of sheet 2 in column N. </p>
  
  <p>Till this i have completed coding. </p>
  
  <p>Now, I want to look into sheet 1, which ever is starting with ID 4, and it is found that It has coressponding D2C Id in sheet2, then it should be copied to column M of sheet1, if not found, then ID of Column L of sheet1 has to be copied in column M. Could anyone guide me , how i can do this </p>
  
  <p>Below, is the code, i used for checking the value from sheet1 and pasting in sheet2.</p>
  
  <pre><code>Sub lookuppro()
  Dim totalrows As Long
      Dim Totalcolumns As Long
      Dim rng As range
  
     totalrows = ActiveSheet.UsedRange.Rows.Count
      Sheets("Sheet2").Select
       For i = 1 To totalrows
       Set rng = Sheets("Sheet1").UsedRange.Find(Cells(i, 12).Value)
       'If it is found put its value on the destination sheet
  
         If Not rng Is Nothing Then
           Cells(i, 13).Value = rng.Value
            End If
           Next
  End Sub
  </code></pre>
  
  <p>below is the code, i used for checking if they are matching and pasting the corresponding D2C number in sheet2. </p>
  
  <pre><code>Sub match()
  Dim i               As Long
      Dim lngLastRow      As Long
      Dim ws              As Worksheet
  
      lngLastRow = range("A1").SpecialCells(xlCellTypeLastCell).Row
  
      Set ws = Sheets("Sheet2")
  
      With ws
  
  
          For i = 1 To lngLastRow
              If .Cells(i, 12).Value = .Cells(i, 13).Value Then
                  .Cells(i, 14).Value = .Cells(i, 1).Value
  
              Else
               'nothing
              End If
          Next i
      End With
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/9JXfj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/9JXfj.png" alt="This is the sample screenshot of sheet1 and the result i am looking for"></a>
  <a href="https://i.stack.imgur.com/dVHqb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dVHqb.png" alt="Is the Image of sheet2."></a></p>