This function is used to search for a keyword in a description and return a corresponding value from a rules table.

It is one of my favorite and most useful automations. Essentially, it replicates what QuickBooks Online (QBO) does when categorizing bank transactions—except it works in Excel, making it ideal for situations where the client cannot provide the statement in QBO format. This function streamlines transaction classification, ensuring efficiency and accuracy even when automation support is unavailable.

📘 Description

The FindRule function is useful for automating the categorization of descriptions based on predefined rules in a table.

• Parameters:
  • description: The string where the keyword will be searched.
  • rulesRange: The range of cells containing the keywords and associated values.
  • resultColumn: The column number (within the range) that contains the value to be returned.

📂 Application

You can use FindRule to:

• Classify bank transactions based on descriptions.
• Map keywords to categories in reports.

📋 Example of a Rules Table

🛠 VBA Code

Function FindRule(description As String, rulesRange As Range, resultColumn As Integer) As Variant
    Dim data As Variant
    Dim keyword As Variant
    Dim i As Long

    ' Carrega os dados do intervalo em um array
    data = rulesRange.Value

    ' Percorre os dados
    For i = LBound(data, 1) To UBound(data, 1)
        keyword = data(i, 1)
        If InStr(1, description, keyword, vbTextCompare) > 0 Then
            FindRule = data(i, resultColumn)
            Exit Function
        End If
    Next i

    ' Retorna vazio se não encontrar correspondência
    FindRule = Empty
End Function