title: Using VLOOKUP in an Excel 2007 VBA Function
body: <p>I have tried hours and hours of examples but I continually get the same error of #NAME?</p>
  
  <p>I need to use VLOOKUP within a VBA function and then work with the result before putting the result to a cell. Firstly I am having a problem just to get the VBA VLOOKUP part working.</p>
  
  <p>For testing here is the details
  Spreadsheet is...</p>
  
  <p>Worksheet = Sheet1</p>
  
  <p>4 rows x 2 columns of data. 
      Cells D1:E4
      Dance : 23
      French : 42
      English : 2
  Music : 33</p>
  
  <p>In cell A1 I will have a user imputed content that will a value from column D eg French</p>
  
  <p>In Cell B1 I will call the function 
      =GetQty(A1,D1:E4,2)
  but I always get #NAME?</p>
  
  <p>1) How do I call the function properly. Am I doing this corretly?</p>
  
  <p>In my VBA function "GetQty" I just want to do the VLOOKUP on a value of cell A1 and return the value from the matching entry from the table(Cell D1:E4), eg return 42 and then place that value into column B1 and (also column C1 via the VBA and not just using a formula in the worksheet cell of =B1 )</p>
  
  <p>Here is one of the many examples I have tried in my function just to get the VLOOKUP to work.</p>
  
  <pre><code>Function GetQty(Celly As Range, CellyRange As Range, Colretval As Integer) As Integer
  Dim result As String
  Dim sheet As Worksheet
  Set sheet = ActiveWorkbook.Sheets("Sheet1")
  GetQty = Application.WorksheetFunction.VLookup(sheet.Range(CellyRange), sheet.Range(CellyTable), Colretval, False)
  End Function
  </code></pre>
  
  <p>I have tried many examples but I always get #NAME? error</p>