VLOOKUP Macro Workbook Reference Variable

crobinson661

New Member
Joined
Nov 15, 2019
Messages
10
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.


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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,188
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Since the variable is a Workbook object, you need to use its Name property to write that into the formula:

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

crobinson661

New Member
Joined
Nov 15, 2019
Messages
10
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.

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

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

New Member
Joined
Nov 15, 2019
Messages
10
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:

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

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,188
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Oops - you're missing a worksheet name in there.

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

Forum statistics

Threads
1,078,442
Messages
5,340,303
Members
399,364
Latest member
farhan11941234

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top