title: vba excel vlookup: lookup value in stored array, lookup table in excel, output value to array
body: <p>I am using vlookup to populate an array in vba.</p>
  
  <p>The array (InfArray) is an ~100 row by 3 column array.  The first column of the array is already populated with integer values.  These integer values correspond to a name.  In one of my excel sheets I have a table that has each integer id listed with its corresponding name.  So I want to use the vlookup function to check the id integer and populate the second column of the array with the actual name.</p>
  
  <p>Here is the code statement I have to assign the second column (original bad code):</p>
  
  <pre><code>For y = 1 To UBound(InfArray,1)
      Set InfArray(y,2) = Application.WorksheetFunction.VLookup(InfArray(y,1), NameSheet.Range("B2:C244"), 2, False).Value
  Next y
  </code></pre>
  
  <p>It is giving me a Object variable or With block variable not set error.</p>
  
  <p>When I cursor over the code text after running, I see that the </p>
  
  <pre><code>NameSheet.Range("B2:C244")
  </code></pre>
  
  <p>is where the object variable or with block variable not set error is located.  This is really confusing.  I put a </p>
  
  <pre><code>Debug.Print NameSheet.Name
  </code></pre>
  
  <p>In the code prior to the error and it is giving me the correct name of the excel sheet in question.  So the code must be looking in the right worksheet, but somehow it is not getting the range...?</p>
  
  <p>I'm lost, please help</p>
  
  <p>EDIT: The error was a typo in the code</p>
  
  <pre><code>NameSheet.Range("B2:C244")
  </code></pre>
  
  <p>should have been </p>
  
  <pre><code>NamesSheet.Range("B2:C244")
  </code></pre>
  
  <p>Note the 's' at the end of NameS</p>
  
  <p>It now works with the edits suggested </p>
  
  <p>Working code line:</p>
  
  <pre><code>For y = 1 To UBound(InfArray,1)
      InfArray(y,2) = Application.WorksheetFunction.VLookup(InfArray(y,1), NamesSheet.Range("B2:C244"), 2, False)
  Next y
  </code></pre>
  
  <p>Apologies for the stoopid error.</p>
  
  <p>Thanks for you help.</p>