Referencing Dim in VLOOKUP

Status
Not open for further replies.

crobinson661

New Member
Joined
Nov 15, 2019
Messages
11
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.
VLOOKUP Macro Workbook Reference Variable

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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,931
Office Version
365
Platform
Windows
Duplicate VLOOKUP Macro Workbook Reference Variable

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
 

crobinson661

New Member
Joined
Nov 15, 2019
Messages
11
Sorry for that...

I will gladly delete either post; if you could advise me as to where to delete?

Also, as an "MVP" do you happen to have any advice for the original question?

Thanks!

Duplicate VLOOKUP Macro Workbook Reference Variable

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread. Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,931
Office Version
365
Platform
Windows
You don't need to delete anything, I forgot to lock this thread.
 
Status
Not open for further replies.

Watch MrExcel Video

Forum statistics

Threads
1,090,077
Messages
5,412,216
Members
403,423
Latest member
fori_gump

This Week's Hot Topics

Top