Vlookup function in VBA? RRS feed

  • Question

  • Hey Guys, 

    could you help me convert this vlookup function to a macro on excel?  Please!! :) I am new to this =VLOOKUP(J2,'Dec 28 - Jan 10 '!J:J, 1, FALSE)

    I researched online and tried working on it but I get a syntax error not sure what I am doing wrong. Kindly, help 

    Dim result As String

    Dim sheet As Worksheet

    Set sheet = ActiveWorkbook.Sheets("J2")

    result = Application.WorksheetFunction.VLookup(sheet.Range("'Dec 28 - Jan 10 '!"), sheet.Range("J:J"),1False)

    Thursday, January 30, 2014 6:11 PM

All replies

  • Keep the workbook open->press Alt+F11->In VBE menu click Insert->Module->Paste below code.

    Now any cell of that workbook ->press insert function or Shift+F3->Insert Function Dialog appears->Select USer Defined or just type the function name sVLOOKUP and click enter->in arhument you select a cell for which VLookup will get data.[you used J2 in sample]

    Function sVLOOKUP(rInput As Range)
        '=VLOOKUP(J2,'Dec 28 - Jan 10 '!J:J, 1, FALSE)
        Dim ws As Worksheet
        Dim rTable As Range
        Dim lColumn As Long
        Set ws = Worksheets("Dec 28 - Jan 10")
        Set rTable = ws.Range("J:J")
        lColumn = 1
        sVLOOKUP = Application.WorksheetFunction.VLookup(rInput, _
            rTable, lColumn, False)
    End Function

    Best Regards,
    Asadulla Javed, Kolkata
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Sunday, February 2, 2014 6:03 AM