Writing a VLOOKUP function in vba

Question

I m trying to lookup a value on a spreadsheet within a table array using the VLOOKUP function in my vba code   I don t know how to write it correctly   Here is the normal VLOOKUP formula with all the references    VLOOKUP DATA AN2 DATA AA9 AF20 5 FALSE

User · Accepted Answer

Have you tried   Dim result As String  Dim sheet As Worksheet  Set sheet   ActiveWorkbook Sheets  Data    result   Application WorksheetFunction VLookup sheet Range  AN2    sheet Range  AA9 AF20    5  False

User · Answer

Please find the code below for Vlookup   Function vlookupVBA lookupValue  rangeString  colOffset  vlookupVBA     N A  On Error Resume Next Dim table lookup As range Set table lookup   range rangeString  vlookupVBA   Application WorksheetFunction vlookup lookupValue  table lookup  colOffset  False  End Function

User · Answer

Public Function VLOOKUP1 ByVal lookup value As String  ByVal table array As Range  ByVal col index num As Integer  As String         Dim i As Long          For i   1 To table array Rows Count             If lookup value   table array Cells table array Row   i - 1  1  Then                 VLOOKUP1   table array Cells table array Row   i - 1  col index num                  Exit For             End If         Next i          End Function

User · Answer

Dim found As Integer     found   0      Dim vTest As Variant      vTest   Application VLookup TextBox1 Value        Worksheets  Sheet3   Range  A2 A55    1  False   If IsError vTest  Then     found   0     MsgBox   Type Mismatch       TextBox1 SetFocus     Cancel   True     Exit Sub Else      TextBox2 Value   Application VLookup TextBox1 Value        Worksheets  Sheet3   Range  A2 B55    2  False      found   1     End If

User · Answer

As Tim Williams suggested  using Application VLookup will not throw an error if the lookup value is not found  unlike Application WorksheetFunction VLookup    If you want the lookup to return a default value when it fails to find a match  and to avoid hard-coding the column number -- an equivalent of IFERROR VLOOKUP what  where  COLUMNS where   FALSE   default  in formulas  you could use the following function   Private Function VLookupVBA what As Variant  lookupRng As Range  defaultValue As Variant  As Variant     Dim rv As Variant  rv   Application VLookup what  lookupRng  lookupRng Columns Count  False      If IsError rv  Then         VLookupVBA   defaultValue     Else         VLookupVBA   rv     End If End Function  Public Sub UsageExample       MsgBox VLookupVBA  ValueToFind   ThisWorkbook Sheets  ReferenceSheet   Range  A D     Not found    End Sub

User · Answer

How about just using   result    VLOOKUP DATA AN2  DATA AA9 AF20  5  FALSE     Note the   and

[excel] Writing a VLOOKUP function in vba

Examples related to excel

Examples related to vba