Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

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
0
Justincut
Asked:
Justincut
  • 3
  • 2
  • 2
1 Solution
 
gowflowCommented:
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
 
gowflowCommented:
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
 
aikimarkCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
aikimarkCommented:
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
 
JustincutAuthor Commented:
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
 
JustincutAuthor Commented:
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
 
JustincutAuthor Commented:
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now