title: VBA - Vlookup returns on development but not when added to ribbon
body: <p>I've a strange problem. </p>
  
  <p>the following code will run using F8 or pressing the run button on the development module. </p>
  
  <p><strong>But</strong> when added to the excel ribbon as a macro by the following process the vlookup will return #N/A  : </p>
  
  <p>1.right click on the excel toolbar > customize the ribbon</p>
  
  <ol start="2">
  <li><p>choose macro commands </p></li>
  <li><p>add it to a new group.  </p></li>
  </ol>
  
  <p>the code is : </p>
  
  <pre><code> Sub Compare()
  
  'set primary Workbook
  'find last cell'
      Dim WS As Worksheet
      Dim LastCell As Range
      Dim LastCellRowNumber As Long
      Set WS = Worksheets("Sheet1")
          With WS
          Set LastCell = .Cells(.Rows.Count, "A").End(xlUp)
          LastCellRowNumber = LastCell.Row
          'MsgBox (LastCell.Row)
      End With
  
  'Adding Index Column
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  [A2].Formula = "=G2&amp;H2"
  Range("A2:A" &amp; LastCellRowNumber).FillDown
  
  'adding headers
  [Ag1].Value = "Resale"
  [Ah1].Value = "Cost"
  [Ai1].Value = "disti"
  
  'set primary Workbook
   Dim Pri As Workbook
   Set Pri = ActiveWorkbook
  
   'open company quotes
    Workbooks.Open ("R:\company\DATA\company quotes.xlsx")
   'find last cell'
      Dim WSq As Worksheet
      Dim LastCellq As Range
      Dim LastCellRowNumberq As Long
      Set WSq = Worksheets("Quote Summary")
          With WSq
          Set LastCellq = .Cells(.Rows.Count, "A").End(xlUp)
          LastCellRowNumberq = LastCellq.Row
          'MsgBox (LastCell.Row)
      End With
  
  Columns("A:A").Select
  Selection.Insert Shift:=xlToRight
  Dim quotes As Workbook
  Set quotes = ActiveWorkbook
  [A2].Formula = "=J2&amp;B2"
  Range("A2:A" &amp; LastCellRowNumberq).FillDown
  
  Pri.Activate
  Dim i As Integer
  
  For i = 2 To LastCellRowNumber
  
  Dim result As String
  Dim sheet As Worksheet
  Range("AG" &amp; i) = Application.VLookup(Sheet1.Range("A" &amp; i), Workbooks("company quotes.xlsx").Worksheets("Quote Summary").Range("A2:AS" &amp; LastCellRowNumberq), 17, False)
  Range("AH" &amp; i) = Application.VLookup(Sheet1.Range("A" &amp; i), Workbooks("company quotes.xlsx").Worksheets("Quote Summary").Range("A2:AS" &amp; LastCellRowNumberq), 19, False)
  Range("Ai" &amp; i) = Application.VLookup(Sheet1.Range("A" &amp; i), Workbooks("company quotes.xlsx").Worksheets("Quote Summary").Range("A2:AS" &amp; LastCellRowNumberq), 20, False)
  
  
  Next i
  
  
   End Sub
  </code></pre>