none
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
    Answerer