VLOOKUP Macro Workbook Reference Variable

[crobinson661]

Good morning,

I am trying to create a macro that opens reference files and later will pull data out to compare in primary workbook (wkb).

wkb3 is going to be the previous days file in each case, and opening the file works without issue.

I am running into an issue referencing the Workbook variable wkb3 in the IFNA(VLOOKUP formula despite the fact that the workbook is already open.

I have googled tirelessly and have attempted the following syntax:

"=IFNA(VLOOKUP(RC[-5],'[" & wkb3 & "]'!C5:C9,5,0),"""")"
"=IFNA(VLOOKUP(RC[-5],[" & wkb3 & "]!C5:C9,5,0),"""")"
"=IFNA(VLOOKUP(RC[-5],'wkb3'!C5:C9,5,0),"""")"
"=IFNA(VLOOKUP(RC[-5]," & wkb3 & "!C5:C9,5,0),"""")"
"=IFNA(VLOOKUP(RC[-5],["wkb3"]!C5:C9,5,0),"""")"
"=IFNA(VLOOKUP(RC[-5],wkb3!C5:C9,5,0),"""")"

The first syntax is a match for another thread I found here:
http://www.vbaexpress.com/forum/archive/index.php/t-58472.html

but I get a Run-Time error 438: Object doesnt support this property or method

Any help would be appreciated.

Sub Product_Valuation()
'
' Product_Valuation Macro
' Product Valuation
'


'
    FileDate = Format(Date - 1, "DD-MMM-YYYY")
    FilePath = "V:\ParentDirectory\Folder1\Folder2\Folder3\Folder4 " & FileDate & ".csv"
    
    Dim wkb As Workbook
    Set wkb = ActiveWorkbook
    
    Dim wkb2 As Workbook
    Set wkb2 = Workbooks.Open("V:\ParentDirectory\Folder1\Folder2\ProductPriceFile.xlsx")
    
    Dim wkb3 As Workbook
    Set wkb3 = Workbooks.Open(FilePath)


    wkb.Activate
    
    Columns("I:R").Select
    Selection.Delete Shift:=xlToLeft
    Columns("E:E").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("E2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=UPPER(RC[-1])"
Selection.AutoFill Destination:=Range("E2:E" & Range("A" & Rows.Count).End(xlUp).Row)
Range(Selection, Selection.End(xlDown)).Select
    Columns("E:E").Select
    Selection.Copy
    Columns("F:F").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "Upper"
    Range("E3").Select
    
    Range("J2").Select
    Application.CutCopyMode = False
    Application.CutCopyMode = False


    Range("J2").FormulaR1C1 = _
        "=IFNA(VLOOKUP(RC[-5],[" & wkb3 & "]!C5:C9,5,0),"""")"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J80")
    Range("J2:J80").Select

 

[RoryA]

Since the variable is a Workbook object, you need to use its Name property to write that into the formula:

"=IFNA(VLOOKUP(RC[-5],'[" & wkb3.Name & "]'!C5:C9,5,0),"""")"

 

[crobinson661]

Hate to bump, but I'm stuck.....
I posted Friday, but with 56 views, I'm the only post without a reply through Sunday, so I'll try to parse my inquiry.

Range("J2").FormulaR1C1 = _
        "=IFNA(VLOOKUP(RC[-5],[" & wkb3 & "]!C5:C9,5,0),"""")"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J80")
    Range("J2:J80").Select

This IFNA(VLOOKUP attempts to reference another workbook opened earlier in the Macro. I think my syntax is wrong, I've tried several variations and cannot figure out how it's wrong.

Most recently, I am getting "Run Time error 438: Object doesn't support this property or method"

Here's the code that opens the file:

FileDate = Format(Date - 1, "DD-MMM-YYYY")
    FilePath = "V:\ParentDirectory\Folder1\Folder2\Folder3\Folder4 " & FileDate & ".csv"
    
    Dim wkb As Workbook
    Set wkb = ActiveWorkbook
    
    Dim wkb2 As Workbook
    Set wkb2 = Workbooks.Open("V:\ParentDirectory\Folder1\Folder2\ProductPriceFile.xlsx")
    
    Dim wkb3 As Workbook
    Set wkb3 = Workbooks.Open(FilePath)

Thanks for any suggestions

 

[crobinson661]

Thanks for the reply Rory; however I am now getting "Run-Time error '1004': Application-defined or object-defined error"?

Here is the specfic referencing code after adjustment:

FileDate = Format(Date - 3, "DD-MMM-YYYY")
    FilePath = "V:\COOBusinessOffice\Software Asset Management\2019 Robinson\Adobe Daily Output\Adobe Daily " & FileDate & ".csv"
    
    Dim wkb As Workbook
    Set wkb = ActiveWorkbook
    
    Dim wkb2 As Workbook
    Set wkb2 = Workbooks.Open("V:\COOBusinessOffice\Software Asset Management\2019 Robinson\Adobe Per Product Expense List.xlsx")
    
    Dim wkb3 As Workbook
    Set wkb3 = Workbooks.Open(FilePath)

Range("J2").FormulaR1C1 = _
        "=IFNA(VLOOKUP(RC[-5],'[" & wkb3.Name & "]'!C5:C9,5,0),"""")"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J80")
    Range("J2:J80").Select

Since the variable is a Workbook object, you need to use its Name property to write that into the formula:

"=IFNA(VLOOKUP(RC[-5],'[" & wkb3.Name & "]'!C5:C9,5,0),"""")"

 

[RoryA]

Oops - you're missing a worksheet name in there.

"=IFNA(VLOOKUP(RC[-5],'[" & wkb3.Name & "]Folder4 " & FileDate & "'!C5:C9,5,0),"""")"

 

[crobinson661]

That worked great, Thanks for your help!!

Oops - you're missing a worksheet name in there.

"=IFNA(VLOOKUP(RC[-5],'[" & wkb3.Name & "]Folder4 " & FileDate & "'!C5:C9,5,0),"""")"