[VBA] Vlookup formula with Dynamic Workbook

zaeta25

New Member
Joined
Jun 14, 2018
Messages
1
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.
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,472
Office Version
  1. 2010
Platform
  1. Windows
try this ( untested)
Code:
nam = ActiveWorkbook.Name

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

RockandGrohl

Active Member
Joined
Aug 1, 2018
Messages
463
Office Version
  1. 2010
Platform
  1. Windows
try this ( untested)
Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,533
Messages
5,529,395
Members
409,870
Latest member
Well59
Top