Lookups Using Find Function

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video.

Each code block covered in the video is given below.

Want to become an expert at looking up data? The Find Function is a highly versatile way of searching for related data in a worksheet. The most common use case would be to a Vlookup style operation. However, there is so much more that you can do with this function. And we will explore all of it in this video.

Practical Example: Read through a monthly invoice statement, dynamically identify the REAL data range and create a report using various lookups.

Basic Section – Perform Single Lookup

Sub RangeFind_Basics_Single()

Dim findRng As Range

Set findRng = wsStore.Columns("A").Find( _
                                what:="Store00Tesy")

If Not findRng Is Nothing Then
    wsData.Range("E5").Value = findRng.Offset(0, 1).Value
    Else
    wsData.Range("E5").Value = "Not Found"
End If

End Sub

Basic Section – Perform Lookup in a Loop

Sub RangeFind_Basics_Loop()

Dim strStore As String
Dim findRng As Range
Dim i As Long

For i = 5 To 18
    strStore = wsData.Range("C" & i).Value
    Set findRng = wsStore.Columns("A").Find( _
                                    what:=strStore)
    If Not findRng Is Nothing Then
        wsData.Range("E" & i).Value = findRng.Offset(0, 1).Value
    End If
Next i
End Sub

Advanced Section – Parent Sub

Sub BuildReport()

IdentifyAndCopyDataRange

Dim lrow As Long
'lrow = wsReport.Range("A1").CurrentRegion.Rows.Count
lrow = FindLastRow("Report")
Dim sStoreCode As String, sRegion As String
Dim lAmount As Long, sDocket As String, sSalesPerson As String
Dim rngDocket As Range
Dim i As Long
For i = 2 To lrow
'Find Store Name
    sStoreCode = wsReport.Range("C" & i).Value
    wsReport.Range("E" & i).Value = FindLookupValue("StoreMap", 1, sStoreCode, 1)
'Find Region
    wsReport.Range("F" & i).Value = FindLookupValue("StoreMap", 1, sStoreCode, 2)
'Find Sales Persons
    sRegion = wsReport.Range("F" & i).Value
    wsReport.Range("G" & i).Value = FindLookupValue("RegionMap", 2, sRegion, -1)
'Fix Amount Due
    lAmount = wsReport.Range("D" & i).Value
    Set rngDocket = wsReport.Range("B" & i)
    If CheckIfInvoice(rngDocket) = True Then
        wsReport.Range("H" & i).Value = "Invoice"
        wsReport.Range("I" & i).Value = lAmount
    Else
        wsReport.Range("H" & i).Value = "Rebate"
        wsReport.Range("I" & i).Value = -lAmount
End If
    
Next i
End Sub

Advanced Section – Dynamically Identify a Range

Sub IdentifyAndCopyDataRange()

Dim startRow As Long, endRow As Long
Dim findRng As Range

' Find Range Starting Row
Set findRng = wsData.Columns("A").Find( _
                        what:="Date", After:=wsData.Range("A1"), _
                        SearchDirection:=xlNext, lookAt:=xlWhole)
If Not findRng Is Nothing Then
    startRow = findRng.Row + 1
End If
' Find Range Ending Row
Set findRng = wsData.Columns("A").Find( _
                        what:="Please", After:=wsData.Range("A1"), _
                        SearchDirection:=xlPrevious, lookAt:=xlPart)
If Not findRng Is Nothing Then
    endRow = findRng.Row - 1
End If

wsReport.Rows("2:" & wsReport.Rows.Count).Clear
wsData.Range(wsData.Cells(startRow, 1), wsData.Cells(endRow, 4)).Copy _
                                                                                            wsReport.Range("A2")

End Sub

Advanced Section – Identify Last Row with Data

Function FindLastRow(sheet As String) As Long

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(sheet)
Dim lrow As Long
On Error Resume Next
lrow = ws.Cells.Find(what:="*", _
                After:=ws.Range("A1"), _
                SearchDirection:=xlPrevious, _
                lookAt:=xlPart _
                ).Row
On Error GoTo 0

FindLastRow = lrow
End Function

Advanced Section – Create a Generic FIND Function

Function FindLookupValue(sheet As String, col As Long, searchValue As String, off As Long) As Variant
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets(sheet)
    Dim findRng As Range
    Set findRng = ws.Columns(col).Find( _
                                what:=searchValue, lookAt:=xlWhole, SearchDirection:=xlNext)
    
    If Not findRng Is Nothing Then
        FindLookupValue = findRng.Offset(, off).Value
        Else
        FindLookupValue = ""
    End If

End Function

Advanced Section – Search for Text within Text

Function CheckIfInvoice(rngDocket As Range) As Boolean

'Dim rngDocket As Range
'Set rngDocket = wsReport.Range("B2")

Dim fndRng As Range
'Search for Inv
'Set fndRng = rngDocket.Find( _
'                        what:="INV", lookAt:=xlPart)

Set fndRng = rngDocket.Find( _
                        what:="*INV*", lookAt:=xlWhole)
If Not fndRng Is Nothing Then
    CheckIfInvoice = True
    Else
    CheckIfInvoice = False
End If

End Function

Advanced Section – Filter Data

Public Const sSalesPerson As String = "Jack"
Sub FilterReport()
wsFilter.Rows("2:" & wsFilter.Rows.Count).Clear
Dim i As Long, lrow As Long
Dim findRng As Range
Dim firstMatch As String

lrow = FindLastRow("Filter") + 1
'sSalesPerson = "Jack"

Set findRng = wsReport.Columns("G").Find( _
                                what:=sSalesPerson, LookIn:=xlValues, lookAt:=xlWhole)

If Not findRng Is Nothing Then
    wsReport.Range(wsReport.Cells(findRng.Row, 1), wsReport.Cells(findRng.Row, 9)).Copy _
                                wsFilter.Range("A" & lrow)
    firstMatch = findRng.Address
    Do
        Set findRng = wsReport.Columns("G").FindNext(After:=findRng)
        If firstMatch = findRng.Address Then Exit Do
        'lrow = FindLastRow("Filter") + 1
        lrow = lrow + 1
        wsReport.Range(wsReport.Cells(findRng.Row, 1), wsReport.Cells(findRng.Row, 9)).Copy _
                                wsFilter.Range("A" & lrow)
    Loop While firstMatch <> findRng.Address
End If
End Sub

Related Posts