Vlookup with External Sheet in VBA

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
Hello
I hope all of you are doing well
I want to use vlookup from external file = Data and Worksheet = Support
as I already know how to use vlookup but I do not want to use workbook name and Worksheet name in my formula
this is the existing formula. Please help me to Write formula without using Workbook name and Worksheet Name in Formula

Code:
Range("B2").Formula = "=VLOOKUP(A2,'[DATA.xlsm]Support'!$A$2:$B$500,1,0)"
 

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
Excel Max , before implementation I want to add that I want to lookup from my Macro workbook to Simple Excel Workbook so the 1st workbook is Macro enabled WB and 2nd is the Simple which will be opened on 2nd number
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
RoryA, Getting Run-Time error 1004:
Application-Defined or object Defined error
both workbooks are open
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
I have Office 2007 and this works for me.
I don't know what you mean "Simple" . i Suppose you mean "Macro Disabled" .
Before run Workbook1 change this...
[DATA.xlsm] in the vlookup function to the ... for example [DATA.xlsx]
 

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
EXCEL MAX, I need to admit that i am confused about how to use it , mean all these code should placed in my Macro workbook and insert a new module
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

RoryA, Getting Run-Time error 1004:
Application-Defined or object Defined error
both workbooks are open

You didn’t answer my question about the value of the workbook name variable.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows
I understand that you are confused, because this is only example to show that is possible.
It's only answer to your first post.
There is no universal solutions.
We can't resolve problem and give you solution if we don't have enought information.
Solution allways depends what you trying to do and many other things.
Sometimes the solution is not absolutly that what you suppose to be.
We are here to help each other and making a way to the solution for you and everyone who have simmilar problem,
but we only need more precise information.
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
392
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Solution 2.
Try to remove single quotes from formula and write like this.
Range("B2").Formula = "=VLOOKUP(A2,[" & otherWorkbookName & "]Support!$A$2:$B$500,1,0)"
 

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
RoryA, I am not getting your point what is the value of the workbook name variable.
I used the variable as you defined in code
further I tried it on separated sample files too
 

vikas_newports

Board Regular
Joined
May 26, 2016
Messages
121
Office Version
  1. 365
Platform
  1. Windows
EXCEL MAX, the main perpose of this question I want to avoid file names in formula
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
When you get the error and press Debug, if you move the cursor over the otherWorkbookName variable it should show you its current value in a tooltip that pops up. Is it the correct workbook, and does that workbook have a Support worksheet?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,793
Messages
5,638,359
Members
417,023
Latest member
Zimbo38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top