Solved

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

Posted on 2014-11-17
7
248 Views
Last Modified: 2014-11-19
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
0
Comment
Question by:Justincut
  • 3
  • 2
  • 2
7 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40449269
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
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40449290
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
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40449575
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.
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40449641
This is an example of what the Open event might look like.
Private Sub Workbook_Open()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim strFormulaDate As String
    
    Workbooks.Open "C:\Users\AikiMark\Downloads\Daily GSFI " & Format(Date, "ddmmyyyy") & ".xlsm", False
    Set wkb = Workbooks("WTDCommentary.xlsm")
    Set wks = wkb.Worksheets("WTD Commentary")
    Set rng = wks.Cells.Find("Daily GSFI")
    strFormulaDate = rng.Formula
    strFormulaDate = Mid(strFormulaDate, InStr(strFormulaDate, "Daily GSFI ") + 11, 8)
'    Debug.Print strFormulaDate
    
    wks.UsedRange.Replace "Daily GSFI " & strFormulaDate, "Daily GSFI " & Format(Date, "ddmmyyyy")
End Sub

Open in new window

0
 

Author Comment

by:Justincut
ID: 40449742
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?
0
 

Author Comment

by:Justincut
ID: 40449760
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.
0
 

Author Comment

by:Justincut
ID: 40452254
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
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question