VBA Using Variables Inside Formula

[Lewzerrrr]

Hey,

Looked at other threads but can't find a concrete answer.

I need to put the vlookup into a range looking at a closed workbook.

This bit of code will just place this in the range with the variable name inside it instead of what the variable actually holds.
=VLOOKUP(R6C3,INDIRECT("'["& LYS &" Linear & Options Week "& NW &".xlsm]Linear'!$C:$X"),11,0)
LYS variable holds "AW16" and NW variable holds "3" but it just inputs the variable name lol.

.Range(D).FormulaR1C1 = "=VLOOKUP(R6C3,INDIRECT(""'[""& LYS &"" Linear & Options Week ""& NW &"".xlsm]Linear'!$C:$X""),11,0)"

If need help clarifying then I'll try my best!

Sub GetLastYearValues()

Dim WB As Workbook, Sht As Worksheet, Dte As Worksheet
Dim L As String, D As String
Dim NW As String, LYS As String, LS As String


Set Sht = ThisWorkbook.Sheets("Linear")
Set Dte = ThisWorkbook.Sheets("Date & Season")


D = "P6:P30,P32:P37,P40:P41,P43:P46"
L = "V6:V30,V32:V37,V40:V41,V43:V49"
NW = Dte.Range("B9").Value
LYS = Dte.Range("B5").Value
LS = Dte.Range("B4").Value


With Application
    .ScreenUpdating = False
    .DisplayAlerts = False
End With


Dte.Visible = xlSheetVeryHidden


If NW = 1 Or NW = 27 Then


Set WB = Workbooks.Open _
    ("S:\Branch Merchandising\" & LS & "\Linear & Options\Week " & NW & "\" & LS & " Linear & Options Week " & NW & ".xlsm")


    With Sht
        .Range(D).FormulaR1C1 = "=VLOOKUP(R6C3,INDIRECT(""'[""& LS &"" Linear & Options Week ""& NW &"".xlsm]Linear'!$C:$X""),11,0)"
        .Range(L).FormulaR1C1 = "=VLOOKUP(R6C3,INDIRECT(""'[""& LS &"" Linear & Options Week ""& NW &"".xlsm]Linear'!$C:$X""),3,0)"
        .Range("P6:P46").Copy
        .Range("P6").PasteSpecial Paste:=xlPasteValues
        .Range("V6:V49").Copy
        .Range("V6").PasteSpecial Paste:=xlPasteValues
    End With
    
WB.Close savechanges:=False
    
Else


Set WB = Workbooks.Open _
    ("S:\Branch Merchandising\" & LYS & "\Linear & Options\Week " & NW & "\" & LYS & " Linear & Options Week " & NW & ".xlsm")


    With Sht
        .Range(D).FormulaR1C1 = "=VLOOKUP(R6C3,INDIRECT(""'[""& LYS &"" Linear & Options Week ""&NW&"".xlsm]Linear'!$C:$X""),11,0)"
        .Range(L).FormulaR1C1 = "=VLOOKUP(R6C3,INDIRECT(""'[""& LYS &"" Linear & Options Week ""&NW&"".xlsm]Linear'!$C:$X""),3,0)"
        .Range("P6:P46").Copy
        .Range("P6").PasteSpecial Paste:=xlPasteValues
        .Range("V6:V49").Copy
        .Range("V6").PasteSpecial Paste:=xlPasteValues
    End With
    
WB.Close savechanges:=False
    
End If


With Application
    .CutCopyMode = False
    .ScreenUpdating = True
    .DisplayAlerts = True
End With


Sht.Range("A1").Select


End Sub

 

[Lewzerrrr]

Adjusted formula from R6C3 to RC3 to make it relative so the final formula SHOULD be this..

=VLOOKUP(RC3,INDIRECT("'[AW16 Linear & Options Week 3.xlsm]Linear'!$C:$X"),11,0)

However, VBA brings it back to this

=VLOOKUP(RC3,INDIRECT("'["& LYS &" Linear & Options Week "& NW &".xlsm]Linear'!$C:$X"),11,0)

 

[Lewzerrrr]

Realised I had quotes .. so stupid :'(

Should be this

.Range(D).FormulaR1C1 = "=VLOOKUP(RC3,INDIRECT(""'[" & LS & " Linear & Options Week " & NW & ".xlsm]Linear'!$C:$X""),11,0)"