Guinaba
Board Regular
- Joined
- Sep 19, 2018
- Messages
- 217
- Office Version
- 2016
- Platform
- Windows
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.
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