Home / Find / Find as a Function

Find as a Function

In our previous post on Find and FindNext we saw that in combination they can be used to search through large amounts of data very quickly.

In many cases we need to use Find and FindNext on a worksheet. The best way to implement repeatable and automated worksheet based functions is through User Defined Function (UDF). This is just a function written in VBA code that can be used on a worksheet.

When using Find and FindNext within a UDF some implementation issues need to be considered. FindNext does not operate within a UDF and we must change the way we use the Find call to give us the same functionality we acheived using Find and FindNext inside a Procedure for the previous post.

Another option to the use of Find and FindNext within a UDF is the used of standard Excel formula SUMPRODUCT. We will provide an example of this formula in a future post.

Now as for the previous example

We have a list of transactions by date for a series of account numbers. We want to calculate the total transactions for each account number into a summary report.

As there are a number of transactions for each Account Number we need to use Find within a User Defined Function to search through the whole range. The function will scan the range and then calculate the total for the specified account number.

Function curCalcTotalValue (strAcctNumber As String) As Currency
'This routine will search and calculate the total value for the Account Number
Dim intCountItem     As Integer
Dim myResult         As Range
Dim myFirstResult    As Range
    intCountItem = 0
    curCalculateTotalValue = 0
    With Worksheets("Find").Range("a3:c33")
        Set myResult = .Find(strAcctNumber, LookIn:=xlValues)
        If Not myResult Is Nothing Then
            Set myFirstResult = myResult
                intCountItem = intCountItem + 1
                curCalcTotalValue = curCalcTotalValue + myResult.Offset(0, 1).Value
                Set myResult = .Find(myResult, After:=myResult)
           Loop While Not myResult Is Nothing And myResult.Address <> myFirstResult.Address
        End If
    End With
End Function

The result of this Macro is shown here, listing the total of the amounts for each account number. The User Defined Function is specified for each account number.

This calculation is performed in a Function and uses Find to search through the range and calculate the total amount for each account number. In this case the account number becomes a parameter to the function call and provides a simple method of extending the use of the function if new account numbers are included to the list.

If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment.

About Excel Help Desk

Check Also

Calculate Number of Days in another Date Range

A custom function has been developed to calculate the Number of Days that one Date Range is within another. This can be very useful when calculating the overlapping periods.