title: Copying an adjacent cell value only from the same row, and no other rows regardless of duplicate results from Instr Function
body: <p>I am having trouble knowing how to code my dilemma. Below is my current code which works extremely well for comparing <strong>Sheet 3 Col B</strong> with <strong>Sheet 2 Col B</strong>. Once a match is found between both <strong>Col B's</strong>, the code then copies the adjacent cells from <strong>Sheet 3 Col A and C</strong>, and pastes the answer into <strong>Sheet 2 Col A and D</strong> respectively. </p>
  
  <pre><code>Sub ID()
  Dim sheet1 As Worksheet, sheet2 As Worksheet, Sheet3 As Worksheet
      Dim isFound As Boolean: isFound = False
      Set sheet1 = Sheets(1)
      Set sheet2 = Sheets(2)
      Set Sheet3 = Sheets(3)
      Dim Sheet3ColB, Sheet2ColB As Variant
      Dim ii As Long, tt As Long, w As Long: w = 3
      Sheet3ColA = Sheet3.Range("A2:C" &amp; Sheet3.Cells(Sheet3.Rows.Count, 2).End(xlUp).Row).Value2
      Sheet2ColB = sheet2.Range("B3:B" &amp; sheet2.Cells(sheet2.Rows.Count, 2).End(xlUp).Row).Value2
      For ii = LBound(Sheet2ColB) To UBound(Sheet2ColB)
          isFound = False
          For tt = LBound(Sheet3ColA) To UBound(Sheet3ColA)
              'perform case insensitive (partial) comparison
              If InStr(1, LCase(Sheet2ColB(ii, 1)), LCase(Sheet3ColA(tt, 2))) &gt; 0 Then
                   sheet2.Cells(w, 1) = Sheet3ColA(tt, 1)
                   sheet2.Cells(w, 4) = Sheet3ColA(tt, 3)
                  w = w + 1
                  isFound = True
              End If
          Next
          If Not isFound Then
              sheet2.Cells(w, 2) = Sheet2ColB(ii, 1)
              w = w + 1
          End If
      Next
  
  End Sub
  </code></pre>
  
  <p>My only issue is that my data will have some duplicates. So when the Instr function runs, it will return more than one value (only a few times at best), for the single row. But all I need is for the code to copy and paste from the row that it is comparing at that time, and nothing more - So only the information from the row in question. My suggestion would be this, but it is returning an error: </p>
  
  <pre><code>sheet2.Cells(w, 1) = Sheet3ColA(tt &amp; Cells.row, 1)
  sheet2.Cells(w, 4) = Sheet3ColA(tt &amp; Cells.row, 3)
  </code></pre>
  
  <p>All I need it to do is for it to take only the data from the same row in <strong>Sheet 3</strong> and paste that info only into <strong>sheet 2</strong>, ignoring all other duplicates possible above/ below the data. </p>