title: Use VLOOKUP in a dynamic range using VBA
body: <p>I'm trying to set up VB so I can do a VLOOKUP on a dynamic range of values. It'll always start at the same place, but it may end further down based on whatever value is in H4.</p>
  
  <p>EDIT: Here's the code and it works. </p>
  
  <p>Thank you Alex!</p>
  
  <pre><code>Public Sub State()
  
      Dim refRng As Range, ref As Range, dataRng As Range
      Dim i As Variant
      Dim count As Integer
      i = Sheet2.Range("H1").Value
      i = i + 3 'offset of when to start
  
      Set refRng = Sheet2.Range("D8:" &amp; Cells(8, i).Address) '//horizontal range of look up values
      Set dataRng = Sheet13.Range("A:C") '//data block you want to look up value in
  
      For Each ref In refRng
      ref.Offset(1, 0) = Application.WorksheetFunction.VLookup(refRng, dataRng, 2, True)
      Next ref
  End Sub
  </code></pre>