Avatar of Justincut
Justincut
 asked on

How to do a Vlookup from 1 spreadsheet to another using VBA code.

HI Guys, I have 2 Excel attachments , 1 called "Daily GSFI" and 1 called "WTD Commentary". From the WTD Commentary spreadsheet, I want to "View -> Windows -> Activate "Daily GSFI"Spreadsheet -> Create a range from "A3:I36" called "Region" (with which to do a Vlookup on later) -> View -> Windows -> Open "WTD Commentary" -> put a formula in F45 to F56 -> "=Vlookup(B45, Region, 9, False). How do I do this?
The problem is the "Daily GSFI" spreadsheet has a different ending in the name every day, eg, "Daily GSFI 18112014.xlsm" so it needs to defined in a variable.
If the Filename was not variable, the formula would look like this:
=VLOOKUP(B45,'[DailyGSFI.xlsm]Daily GSFI PL'!$A$3:$I$37,9,FALSE)
WTDCommentary.xlsm
DailyGSFI.xlsm
ProgrammingMicrosoft ExcelVisual Basic Classic

Avatar of undefined
Last Comment
Justincut

8/22/2022 - Mon
gowflow

Hello

Looking at your request and looking at the files either I am totally off or you are mixed in the regions and ranges

1) Create a range from "A3:I36" called "Region"
>>>> In what sheet workbook ? Presume in 'Daily GSFI' if yes then region is "A3:I37" and not 36 Correct ?

2) Open "WTD Commentary" -> put a formula in F45 to F56
>>>> Again if we look at sheet WTD Commentary we have data from F45 to F66 so is this the Range or .... ????

gowflow
gowflow

Furthermore,

3) The file you posted if we are to compare the data between WTD Commentary in Row F45 downward and trying to find these values in sheet 'Daily GSFI'  Col I, then it is almost impossible the one and only amount that correspond is 81,900.00 that is the first item and that we can find in E45 and not F45.

Can you please explain the logic of what you are trying to achieve and please leave the filename issue aside and the possible solutions. As we need to understand first what you want and not how you envision resolving your problem.

Regards
gowflow
aikimark

You could make a copy of the current daily file as DailyGSFI.xlsm and then use the formula you posted.  This could possibly be done in a batch file that opens/launches the WTDCommentary workbook.  It might be possible to make the copy in VBA code in the Workbook_Open() event in the WTDCommentary workbook.  I'm less sure about the success of the second method.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ASKER CERTIFIED SOLUTION
aikimark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Justincut

ASKER
Hi, this is the logic. I have to do a Vlookup up from the WTD Commentary spreadsheet to update the Book Value of the Companies in Column B. The spreadsheet I do the lookup to is opened via an e-mail and not saved down to any path. Thus, done manually, I have to do a Vlookup to Column D in the GSFI spreadsheet in Column D and go across to the USD MTM Position column. The GSFI PL has a variable ending too so it could have yesterday's date on the end.using VBA from a Macro in the WTD commentary file, I want to create a Range with which to do a Vlookup in the GSFI file so I can see the formulas in the WTD Commentary file instantly by pushing a Macro. Any ideas?
Justincut

ASKER
The Range called Region I want in the GSFi file, but it must be recognized in the Vlookup formula in the WTD Commentary file.the formulas go in the F45 Cells downwards so it should look it =Vlookup(B45, Region, 12, False). Ignore the fact they are already populated.
Justincut

ASKER
Hi Guys, this line is breaking in the Macro in the "WTD Commentary" spreadsheet.

 ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-4],'[" & myfilename & "]" & MYSHEETNAME & "'!" & myrangename & ",16,FALSE)"

I am getting "Run-time error '1004' Application defined error. Any ideas why?
WTD-Commentary-17112014.xlsm
DAILY-GSFI-PL-20141107.xlsx
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.