none
Reference table colums - vlookup RRS feed

  • Question

  • Hello,

    While searching I've found examples for referencing to a single column in a table, but I've not found any means to referencing to a range of columns by name.

    My goal is to mimic a VLOOKUP as you would perform in Excel formulas in VBA, but in a named table with column names.

    In Excel, using named references I would write "=VLOOKUP(MyVariable;MyTable[[ID]:[Fullname]];3;FALSE)"

    Note that the 'ID' column is not the first column in the table nor is 'Fullname' the last, thus I'm trying to lookup from adjacent columns in the middle of the table.

    Private Sub
    Dim PID, FName As String
    PID = "ABC"
    Fname = Application.WorksheetFunction.VLookup(PID, MySheetName.Range("MyTable[[ID]:[Fullname]]"), 3, False)
    
    MsgBox Fname
    
    End Sub

    When I try to run something like that, I receive Error 424, object required.

    What am I doing wrong?

    Thanks in advance for any insights.

    Monday, November 7, 2016 5:00 PM

Answers

  • Fname = Application.WorksheetFunction.VLookup(PID, ThisWorkbook.Worksheets(MySheetName).Range("MyTable[[ID]:[Fullname]]"), 3, False)


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    I managed to get it working.

    While adding your suggestion resulted in another error  (9, Subscript out of range). It did help me finding a solution. Deleting the entire bolded part solved it. The worksheet should not be included, only the named range being the table.

    Fname = Application.WorksheetFunction.VLookup(PID, Range("MyTable[[ID]:[Fullname]]"), 3, False)

    Thanks for the suggestion.


    • Marked as answer by Sflues Tuesday, November 8, 2016 2:50 PM
    Tuesday, November 8, 2016 2:49 PM

All replies

  • I would recommend using ADODB and connect to a spreadsheet rather than a DB.  You'll use SQL to get the data. Refer to tutorial 93 first and then tutorial 71-92 for details on SQL and ADODB.
    Monday, November 7, 2016 5:17 PM
  • Thank you for your recommendation, but I was hoping to find an Excel/VBA only solution. The file is likely to be opened on PCs where nothing but Excel 2007 or later is installed. (I'm working with Excel 2010).
    Tuesday, November 8, 2016 6:54 AM
  • Fname = Application.WorksheetFunction.VLookup(PID, ThisWorkbook.Worksheets(MySheetName).Range("MyTable[[ID]:[Fullname]]"), 3, False)


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    Tuesday, November 8, 2016 11:32 AM
  • I've used this technique on Excel 2003, 2007 and 2010.  It is all VBA except for a simple SQL string.  You don't have to open the Excel document to extract the data so it is very fast.  You can also write SQL to extract data based on a complex set of conditions.

    • Edited by mogulman52 Tuesday, November 8, 2016 1:14 PM
    Tuesday, November 8, 2016 1:09 PM
  • Fname = Application.WorksheetFunction.VLookup(PID, ThisWorkbook.Worksheets(MySheetName).Range("MyTable[[ID]:[Fullname]]"), 3, False)


    http://www.ambienteoffice.com.br - http://www.clarian.com.br

    I managed to get it working.

    While adding your suggestion resulted in another error  (9, Subscript out of range). It did help me finding a solution. Deleting the entire bolded part solved it. The worksheet should not be included, only the named range being the table.

    Fname = Application.WorksheetFunction.VLookup(PID, Range("MyTable[[ID]:[Fullname]]"), 3, False)

    Thanks for the suggestion.


    • Marked as answer by Sflues Tuesday, November 8, 2016 2:50 PM
    Tuesday, November 8, 2016 2:49 PM