Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

VLookupExt Function - Excel

RSS
Modified on Mon, Oct 21, 2013, 2:58 PM by Administrator Categorized as Microsoft Office
Function VLookupExt(lookupValue As String, tableRange As Range, colNum As Long) As Variant

    On Error GoTo VLookupExt_Error

    Dim result As Variant
    Dim row As Long
    Dim colMin As Long
    Dim colTgt As Long
    Dim rowMin As Long
    Dim rowMax As Long
    Dim s As String
    
    With tableRange
    
        rowMin = .row
        rowMax = rowMin + .Rows.Count - 1
        colMin = .Column
        colTgt = colMin + colNum - 1
    
        With .Worksheet
    
            For row = rowMin To rowMax
                
                With .Range(.Cells(row, colMin), .Cells(row, colMin))
                
                    s = .Value
                    
                    If s = lookupValue Then
                    
                        result = tableRange.Worksheet.Cells(row, colTgt).Value
                        Exit For
                    
                    End If
                
                End With
                
            Next
    
        End With
    
    End With

    VLookupExt = result

Exit Function
VLookupExt_Error:
    Debug.Print Err.Number & " - " & Err.Description
    Exit Function
    Resume

End Function

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2018, Patrick Jasinski.