title: VLookup works in cell but not in macro
body: <p>I'm having a problem where moving a VLookup function from a Cell to a Macro is returning #N/A. </p>
  
  <p>Right now, I have a table that is referencing a second, unformatted table to build a formatted list. The formatted list uses a series of VLOOKUP commands to  populate, but this requires manually entering a new line and the 'SubPart' name.</p>
  
  <p>I'm writing a macro that will simply read the whole source table and populate thew new formatted one with a single click. </p>
  
  <p>Unfortunately, I can't seem to get VLOOKUP to work when it's taken from cells to a macro. </p>
  
  <pre><code>Sub PopulateSubs()
  
  
  On Error GoTo MyErrorHandler:
  Dim SubAssy, SubPart As String
  Dim BomCount, i As Integer
  Dim BlankRow As Long
  Dim Source_Table As ListObject
  
  Set Source_Table = Worksheets("BoMs").ListObjects("Table_Query_From_Syspro")
  
  
  SubAssy = InputBox("Enter the Subassembly:")
  
  If Len(SubAssy) &gt; 0 Then
    BomCount = Application.CountIf(Range("Table_Query_from_Syspro[ParentPart]"), SubAssy)
  
   For i = 1 To BomCount
  
      BlankRow = Range("A10000").End(xlUp).Row + 1
      Cells(BlankRow, 1).Select
      SubPart = SubAssy &amp; i
      ActiveCell = i
      ActiveCell.Offset(0, 1).Select
      ActiveCell = Application.WorksheetFunction.VLookup(SubPart, Source_Table, 2, 0)
      ActiveCell.Offset(0, 1).Select
      ActiveCell = Application.WorksheetFunction.VLookup(SubPart, Source_Table, 3, 0)
  
  Next i
  
  Else
  
      MsgBox "You entered an invalid value"
  
  End If
  
  
  MyErrorHandler:
  If Err.Number = 1004 Then
  MsgBox "Sub assembly Not Present in the table."
  End If
  
  End Sub       
  </code></pre>
  
  <p>VLOOKUP in the manual table was correctly working, but I cannot get it to return anything but #N/A in this application.</p>
  
  <p>I have looked at similar solutions on this site, and run the macro with SubPart as "SubPart" as well as ""SubPart"" but neither term seems to help.</p>
  
  <p>The source table is formatted as General for all cells, I'm not sure if this causes a type mismatch with String entries.</p>
  
  <p>Thanks!</p>