VBA VLookup

[Guinaba]

Hi all,

I am trying to use a vba vlookup to get some data from a close sheet, I am guessing I am missing something in the vlookup value, when I hardcode the value using double quotes like ""A"" the formula works but when I get my varaible vlkup there doesn't do anything.

Option Explicit
Public Sub Example()
    
    Dim ws1 As Worksheet
    Set ws1 = ThisWorkbook.Worksheets("Template")

    Dim LRow As Integer
    LRow = ws1.Cells(ws1.Rows.Count, "A").End(xlUp).row
    
    Dim LCol As Integer
    LCol = ws1.Cells(5, ws1.Columns.Count).End(xlToLeft).Column
    
    Dim VlkupRng As Range
    Set VlkupRng = ws1.Range("A5", ws1.Cells(LRow, LCol))
      
    Dim path As String 'Getting Scan data
    path = "\\bfgfs\Supply Chain\Demand Planning\Coles\Promo Review Report\"
    
    Dim r As Long
    Dim c As Long
    Dim Vlkup As Variant
    
    For r = 6 To VlkupRng.Rows.Count
        
        For c = 6 To VlkupRng.Columns.Count
        
        Vlkup = ws1.Cells(r, 2) & ws1.Cells(r, 5) & Format(ws1.Cells(5, c), "d/mm/yyyy")
        
            If Cells(r, 5).Value = "Coles Scan Sales" Then
                ws1.Cells(r, c).Formula = "=VLOOKUP(Vlkup,'" & path & "[01_Coles_Scan_Sales_All.xlsx]Sheet1'!$E:$O,11,0)"
            End If
    
        Next c
        
    Next r
    
End Sub

 

[mohadin]

Hi
It should Be something like this (not tested)

ws1.Cells(r, c).Formula = "=VLOOKUP(" & Vlkup & ",'" & path & "[01_Coles_Scan_Sales_All.xlsx]Sheet1'!$E:$O,11,0)"

Cause Vlkup in yours is not considered as variable

 

[Guinaba]

Thanks @mohadin! I can see the value of the variable now using your suggestion, however I am getting the error:

Run-time error '1004': Application-defined or object-defined error

I may also have these errors because I am missing more ampersands (&) in my code?

ws1.Cells(r, c).Formula = "=VLOOKUP(" & Vlkup & ",'" & path & "[01_Coles_Scan_Sales_All.xlsx]Sheet1'!$E:$O,11,0)"

 

[offthelip]

The easy way to find an error when you are writing formula with VBA is to omit the equals sign so that you just write text to the cell. Then go to the cell manually add the equals sign back in , and excel will highlight where the error is

 

[Guinaba]

The easy way to find an error when you are writing formula with VBA is to omit the equals sign so that you just write text to the cell. Then go to the cell manually add the equals sign back in , and excel will highlight where the error is

Thanks @offthelip!! Great tip! I found the issue, just missing some double quotes