title: Excel VBA:Looping a Vlookup
body: <p>I'm new to VBA and I'm trying to convert a vlookup formula I wrote to VBA code that will loop through each cell in a column. The vlookup is part of an if statement, which works fine on its own. The problem I'm facing is that I don't know how to tell the vlookup function what cells to look for data in so that it can be generalized to the entire loop. The formula is the following  </p>
  
  <p>=IF(K114&lt;>"",K114,VLOOKUP($A114,comp!$A$1:$D$136,2,FALSE))</p>
  
  <p>Here's what I have for the VBA</p>
  
  <pre><code>Sub Ranking()
  
  Dim cell As Range, rng As Range
  Set rng = Range("K2:K120")
  
  For Each cell In rng
      If cell.Value &lt;&gt; "" Then
          cell.Offset(0, 1).Value = cell.Value
      Else: cell.Offset(0, 1).Value = WorksheetFunction.VLookup(HELP HERE,ThisWorkbook.Sheets(2).Range("A1:D136"), 2, 0)
      End If
  Next
  End Sub
  </code></pre>
  
  <p>I'm not sure what to put where I put "help here" to tell it to refer to the text in column A of the same row it's looking in. I'm also not sure if this is the right way to loop for this situation?  Any suggestions are greatly appreciated. Thanks so much!</p>