VBA VLookup

Guinaba

Board Regular
Joined
Sep 19, 2018
Messages
83
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.

VBA Code:
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
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
895
Office Version
  1. 2013
Platform
  1. Windows
Hi
It should Be something like this (not tested)
VBA Code:
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

Board Regular
Joined
Sep 19, 2018
Messages
83
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,646
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
Sep 19, 2018
Messages
83
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,588
Messages
5,597,049
Members
414,117
Latest member
marblepoint

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top