Macro To Add VLOOKUP Formula To lookup Another Workbook

  • I'm running against this problem: a file should be run whenever people want.
    however, the last part to complete the file is that the file from yesterday should be opened.
    From this file data will be extracted via the VLookup formula...that's the plan.
    However, with dim statements it won't work.
    for now the code is:


    the "this1, that1" etc appear when you point with your mouse in the code as correct but
    the VLookup function won't work: it takes a long time and when interupted the macro the cells show the VLookup formula with "[that1]" instead of the workbook's name.
    I also tried to dim that1 as workbook but that did not help.


    Obviously I'm doing something wrong.
    I appreciate your help with this.
    Thanks in advance.

  • Re: Macro To Add VLOOKUP Formula To lookup Another Workbook


    I think you just need to separate out the variable in the string...


    try;

    Code
    'Search for comments last week from last week's file
    Workbooks(this1).Activate 
    With Range("P2") 
        Range(.Cells(1, 1), .End(xlDown)).Offset(0, 1).Select 
        Selection.FormulaR1C1 = _ 
        "=VLOOKUP(RC[-8],'[" & that1 & "]Raw'!C9:C18,10,0)" 
        Selection.Value = Selection.Value 
    End With 
    Windows(that1).Activate 
    ActiveWorkbook.Close


    Hope that does it.
    Ian

  • Re: Macro To Add VLOOKUP Formula To lookup Another Workbook


    Forgot to mentioned this: I already tried that as I saw it in another thread but it did not work

  • Re: Macro To Add VLOOKUP Formula To lookup Another Workbook


    Try:


    Code
    'Open the file from yesterday. Define the  date string of this week and last week
    mynum = Format(Date, "yyyy" & "-" & "mm" & "-" & "dd") & ".xls" 'mynum is a date string like 2008-06-26
    mysearch = Format(Date - 1, "yyyy" & "-" & "mm" & "-" & "dd") & ".xls"                     
    this1 = "Outstanding invoices " & mynum
    that1 = "Outstanding invoices " & mysearch

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!