none
unable to get the VLookup property of the worksheet function class RRS feed

  • Question

  • can I use the application.worksheetfunction.vlookup to perform a look up in a different workbook & worksheet than the one the function resides in? does it matter if the workbook is open or not?

    steve

    Saturday, January 19, 2013 2:46 AM

Answers

  • Unless you've "declared" the arguments of the function, Excel won't display them for a user-defined function.

    Try changing the lines

        UVe = Application.WorksheetFunction.VLookup(EndDate, LookupRange, 2, False)
        UVb = Application.WorksheetFunction.VLookup(BeginDate, LookupRange, 2, False)

    to

        UVe = Application.WorksheetFunction.VLookup(CDbl(EndDate), LookupRange, 2, False)
        UVb = Application.WorksheetFunction.VLookup(CDbl(BeginDate), LookupRange, 2, False)


    Regards, Hans Vogelaar

    • Marked as answer by steve99g Saturday, January 26, 2013 7:44 PM
    Sunday, January 20, 2013 10:43 PM

All replies

  • You can look up values in another OPEN workbook:

    x = Application.VLookup(Range("A1"), _
        Workbooks("OtherWorkbook.xlsx").Worksheets("Sheet1").Range("A1:B100"), _
        2, False)
    But not in a workbook that is not currently open in Excel.

    Regards, Hans Vogelaar


    Saturday, January 19, 2013 3:00 AM
  • I am in the soup. Here's what I did, yet I still get the error:

    I have a sheet with 2 columns, column 1 has dates in mm/dd/yyyy format, column 2 has 5 decimal numbers. below is the function & cell entry I use to call it. The module is in the same workbook as the sheet. I have two questions:

    1. I still get the error 1004 unable to get the vlookup property...
    2. when I begin typing the function into the cell, none of the agruements appear in the intellisense. Any help will be appreciated

    =ReturnCalcTest(a10,a2,$a1:$b80)

    Public Function ReturnCalcTest(EndDate As Date, BeginDate As Date, LookupRange As Range) As Double

    Dim UVe, UVb As Double
    Dim i As Integer

    i = DateDiff("yyyy", EndDate, BeginDate)
    UVe = Application.WorksheetFunction.VLookup(EndDate, LookupRange, 2, False)
    UVb = Application.WorksheetFunction.VLookup(BeginDate, LookupRange, 2, False)

    If i < 1 Then
        ReturnCalcTest = ((UVe / UVb) - 1) * 100
    Else
        ReturnCalcTest = (((UVe / UVb) ^ (1 / i)) - 1) * 100
    End If

    End Function


    steve

    Sunday, January 20, 2013 8:26 PM
  • Unless you've "declared" the arguments of the function, Excel won't display them for a user-defined function.

    Try changing the lines

        UVe = Application.WorksheetFunction.VLookup(EndDate, LookupRange, 2, False)
        UVb = Application.WorksheetFunction.VLookup(BeginDate, LookupRange, 2, False)

    to

        UVe = Application.WorksheetFunction.VLookup(CDbl(EndDate), LookupRange, 2, False)
        UVb = Application.WorksheetFunction.VLookup(CDbl(BeginDate), LookupRange, 2, False)


    Regards, Hans Vogelaar

    • Marked as answer by steve99g Saturday, January 26, 2013 7:44 PM
    Sunday, January 20, 2013 10:43 PM
  • Thank you Hans

    steve

    Saturday, January 26, 2013 7:44 PM