VBA - Vlookup returns on development but not when added to ribbon

Question

I've a strange problem.

the following code will run using F8 or pressing the run button on the development module.

But when added to the excel ribbon as a macro by the following process the vlookup will return #N/A :

1.right click on the excel toolbar > customize the ribbon

choose macro commands
add it to a new group.

the code is :

 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&H2"
Range("A2:A" & 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&B2"
Range("A2:A" & LastCellRowNumberq).FillDown

Pri.Activate
Dim i As Integer

For i = 2 To LastCellRowNumber

Dim result As String
Dim sheet As Worksheet
Range("AG" & i) = Application.VLookup(Sheet1.Range("A" & i), Workbooks("company quotes.xlsx").Worksheets("Quote Summary").Range("A2:AS" & LastCellRowNumberq), 17, False)
Range("AH" & i) = Application.VLookup(Sheet1.Range("A" & i), Workbooks("company quotes.xlsx").Worksheets("Quote Summary").Range("A2:AS" & LastCellRowNumberq), 19, False)
Range("Ai" & i) = Application.VLookup(Sheet1.Range("A" & i), Workbooks("company quotes.xlsx").Worksheets("Quote Summary").Range("A2:AS" & LastCellRowNumberq), 20, False)


Next i


 End Sub

It might not be the source of your problem but you have some referencing issues as you're not specifying the `Workbook` or `Worksheet` for a lot of your references. Also, you should avoid using `Select`, see here: http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros for some more info — Jordan, May 08 '17 at 15:12
I wasn't using select, but i do think its a referencing issue, maybe when i put the vba in the ribbon , it has a different referencing . but i don't understand well enough how to overcome this referencing issue . what should i do to set the reference ? — Noam, May 09 '17 at 06:30

score 0 · Answer 1 · answered May 09 '17 at 07:20

I've tried to fix any referencing issues I could find but you'll need to have a look through and make sure all of the Range references are prefixed with the correct Workbook and Worksheet as it wasn't too clear which worksheet they were coming from in the original code:

Sub Compare()

'Set primary Workbook
'Find last cell
Dim WS As Worksheet
Dim LastCellRowNumber As Long
Set WS = ThisWorkbook.Sheets("Sheet1")
LastCellRowNumber = WS.Cells(WS.Rows.Count, "A").End(xlUp).Row
'MsgBox (LastCell.Row)

'Adding Index Column
WS.Columns("A:A").Insert Shift:=xlToRight
WS.Range("A2:A" & LastCellRowNumber).Formula = "=G2&H2"

'adding headers
WS.Range("AG1").Value = "Resale"
WS.Range("AH1").Value = "Cost"
WS.Range("AI1").Value = "disti"

'open company quotes
Dim wbCompQuotes As Workbook
Set wbCompQuotes = Workbooks.Open ("R:\company\DATA\company quotes.xlsx")

'find last cell'
Dim wsQuoteSum As Worksheet
Dim LastCellRowNumberq As Long
Set wsQuoteSum = wbCompQuotes.Worksheets("Quote Summary")
LastCellRowNumberq = wsQuoteSum.Cells(wsQuoteSum.Rows.Count, "A").End(xlUp).Row
'MsgBox (LastCell.Row)

wsQuoteSum.Columns("A:A").Insert Shift:=xlToRight
wsQuoteSum.Range("A2:A" & LastCellRowNumberq).Formula = "=J2&B2"

Dim i As Long

For i = 2 To LastCellRowNumber
    WS.Range("AG" & i) = Application.VLookup(WS.Range("A" & i), wsQuoteSum.Range("A2:AS" & LastCellRowNumberq), 17, False)
    WS.Range("AH" & i) = Application.VLookup(WS.Range("A" & i), wsQuoteSum.Range("A2:AS" & LastCellRowNumberq), 19, False)
    WS.Range("AI" & i) = Application.VLookup(WS.Range("A" & i), wsQuoteSum.Range("A2:AS" & LastCellRowNumberq), 20, False)
Next i

End Sub

still doesn't work when i use it as a ribbon button . :\ – Noam May 09 '17 at 13:00 — Noam, May 09 '17 at 13:00

VBA - Vlookup returns on development but not when added to ribbon

1 Answers1