[VBA] Vlookup formula with Dynamic Workbook

[zaeta25]

Hello,

I have a problem setting a Vlookup formula with VBA.
When I run the Macro a fileDialog appears asking me for the location of the file several times but that happens just when I use (File as the workbook)

This is my formula

Public File As a Workbook

Dim NBF As Range

Set File = ActiveWorkbook

Workbooks(Template).Worksheets("Data ").Range(NBF.Address).Formula = "=VLOOKUP(C2,'[File]AP'!$H:$L,5,FALSE)"

---------
Now When I use the following code with the name of the file I don't have problems

Workbooks(Template).Worksheets("Data ").Range("K2").Formula = "=VLOOKUP(C2,'[100-583 ACC ES.xlsx]AP'!$H:$L,5,FALSE)"

I will have Dynamic workbook so I don't know how to configure the formula and prevent the Pop up asking for the file location.

Thanks for your help, suggestions or examples.

 

[offthelip]

try this ( untested)

nam = ActiveWorkbook.Name

Workbooks(Template).Worksheets("Data ").Range(NBF.Address).Formula = "=VLOOKUP(C2,'[" & nam & "]AP'!$H:$L,5,FALSE)"

 

[RockandGrohl]

try this ( untested)

nam = ActiveWorkbook.Name

Workbooks(Template).Worksheets("Data ").Range(NBF.Address).Formula = "=VLOOKUP(C2,'[" & nam & "]AP'!$H:$L,5,FALSE)"

This actually worked for me, I kept getting Application Error (1004) but as soon as I set activeworkbook.name, it's all worked out quite nicely. Thanks!