Vlookup referencing another workbook in vba macro

[agohir]

Hi,

I'm trying to reference another workbook in vlookup formula but I get an error. Is there a way I can reference another workbook without having the workbook open. I tried two formula's below (1. setting workbook as variable and 2. putting workbook directory in the vlookup itself).:

1)
Dim MasterTracker As String
MasterTracker = "U:\OPERATIONS\Working Folder\[#Master Tracker.xlsm]"

Range("AA2").Select
Active.Formula = "=IF(VLOOKUP(VLOOKUP(W2,'" & MasterTracker & "Current CDM Plan #'!$B:$C,2,FALSE)&W2&V2,'" & MasterTracker & "Database'!$BE:$BF,2,FALSE)>X2,""Flag"","""")"

2)
Active.Formula = "=IF(VLOOKUP(VLOOKUP(W2,'U:\OPERATIONS\Working Folder\[#Master Tracker.xlsm]Current CDM Plan #'!$B:$C,2,FALSE)&W2&V2,'U:\OPERATIONS\Working Folder\[#Master Tracker.xlsm]Database'!$BE:$BF,2,FALSE)>X2,""Flag"","""")"


The formula works in excel, its just when I try in vba it gives error.

Please help. Thanks!

 

[agohir]

I think I found the error myself. The error was I wrote Active.Formula. The correction is ActiveCell.Formula

Hi,

I'm trying to reference another workbook in vlookup formula but I get an error. Is there a way I can reference another workbook without having the workbook open. I tried two formula's below (1. setting workbook as variable and 2. putting workbook directory in the vlookup itself).:

1)
Dim MasterTracker As String
MasterTracker = "U:\OPERATIONS\Working Folder\[#Master Tracker.xlsm]"

Range("AA2").Select
Active.Formula = "=IF(VLOOKUP(VLOOKUP(W2,'" & MasterTracker & "Current CDM Plan #'!$B:$C,2,FALSE)&W2&V2,'" & MasterTracker & "Database'!$BE:$BF,2,FALSE)>X2,""Flag"","""")"

2)
Active.Formula = "=IF(VLOOKUP(VLOOKUP(W2,'U:\OPERATIONS\Working Folder\[#Master Tracker.xlsm]Current CDM Plan #'!$B:$C,2,FALSE)&W2&V2,'U:\OPERATIONS\Working Folder\[#Master Tracker.xlsm]Database'!$BE:$BF,2,FALSE)>X2,""Flag"","""")"


The formula works in excel, its just when I try in vba it gives error.

Please help. Thanks!