title: VBA Excel Look for values between 2 dynamic ranges
body: <p>For each cell in 'myRange' I want to check for a range of values in Sheet2 and if the values in Sheet2 are found in myRange then for the corresponding row I want to put the value from Column A into Column E</p>
  
  <p>As it stands, I'm only able to look for a single value from Sheet2 ("A1"). When attempting to extend this range I get errors.</p>
  
  <p>Is there a way to make the range in Sheet2 dynamic, please?</p>
  
  <pre><code>Sub Find_values()
  
      Dim myRange As Range
      Dim Cell As Range
      Dim LR As Long
  
      LR = Sheets(1).Range("B" &amp; Rows.Count).End(xlUp).Row
      Set myRange = Sheets(1).Range("B1:B" &amp; LR)
  
      For Each Cell In myRange
  
      If Cell.Value = Sheets(2).Range("A1").Value Then Cell.Offset(0, 3) = Cell.Offset(0, -1).Value
  
  
      Next Cell
  
  End Sub
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/nieFj.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nieFj.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/Q7sJH.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/Q7sJH.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/FGGNi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/FGGNi.png" alt="enter image description here"></a></p>
  
  <p><a href="https://i.stack.imgur.com/XhzV0.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XhzV0.png" alt="enter image description here"></a></p>