How to use Named Range in Excel VLookup Formula RRS feed

  • Question

  • Hi Forum,

    I am doing XL automation using visual studio 2015 community version,

    I am struggling more than a day, to get the solution for the below problem.

    Dim lookup_value As String = Msht.Range("B" & Mshtlr + 1).Value
    xlapp.ActiveWorkbook.Names.Add(Name:="LookupRange", RefersToR1C1:="=" & CkSht.Name & "!R1C1:R" & CommonExcelFunctions.lastrow(CkSht,, "E") & "C5")
                xlapp.Range("C" & Mshtlr + 1).Value = xlapp.WorksheetFunction.VLookup(lookup_value, xlapp.Range("LookupRange"), 4, False)

    I am continuously getting the below error

    An unhandled exception of type 'System.Runtime.InteropServices.COMException' occurred in mscorlib.dll

    Additional information: VLookup method of WorksheetFunction class failed

    "lookup_value" is set correctly.

    "LookupRange" is also set correctly in the expected sheet.

    Any reply on the above question is appreciated.

    I am not able to use "WorksheetFunction.Indirect"

    Thanks in Advance


    • Moved by Reed KimbleMVP Friday, May 26, 2017 1:47 PM excel specific/likely not VB related
    Friday, May 26, 2017 1:40 PM

All replies

  • Since the problem is with the VLookup() method call, you may get better support from the Excel for Developers forum.  I will move this thread to that forum and another moderator can move it back if they find that the problem does not lie in the VLookup method call or its parameter values.

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, May 26, 2017 1:46 PM
  • Hi -

    Suggest that instead of "xlapp.Range", which I assume xlApp is the Excel Application and not either the workbook or worksheet you intend, that you use "xlapp.ActiveSheet.Range"  and "xlapp.ActiveWorkbook.Range" in your last line:

    xlapp.ActiveSheet.Range("C" & Mshtlr + 1).Value = xlapp.WorksheetFunction.VLookup(lookup_value, xlapp.ActiveWorkbook.Range("LookupRange"), 4, False)

    It may be more useful to use Sheets("<TheSheetNameTheValueShouldGoOn>") instead of ActiveSheet


    -MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com. For any reply that either helps to answer your question or is the answer, please mark it as helpful or as the answer so others with the same question will have an answer quickly.

    • Edited by MainSleuth Friday, May 26, 2017 2:54 PM Couldn't BUY a new line
    Friday, May 26, 2017 2:53 PM
  • Hi Thiyagarajan,

    RefersToRange returns the Range object referred to by a Name object. So you could use RefersToRange property to get the named range.

    Here is the example.

    Debug.Print WorksheetFunction.VLookup("a", ActiveWorkbook.Names("LookupRange").RefersToRange, 2, False)

    Best Regards,


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Chenchen Li Thursday, June 1, 2017 9:06 AM
    Monday, May 29, 2017 6:24 AM