Home / Find / Find and FindNext

Find and FindNext

For this 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 a combination of Find and FindNext to continue searching through the whole range. The routine below will scan the range and then calculate the total for the specified account number, it will also calculate the number of records found for that account number.

Sub CalculateTotalValue()
'This routine will search and calculate the total value for the Account Number
 
Dim intCountItem                As Integer
Dim strAccountNumber            As String
Dim myResult                    As Range
Dim myFirstResult               As Range
Dim curTotalAmount              As Currency
 
    	'Set the value to be searched for in this routine
    	strAccountNumber = "57894321"
	intCountItem = 0
 
	With Worksheets("Find").Range("a3:c33")
 
	Set myResult = .Find(strAccountNumber, LookIn:=xlValues)
		If Not myResult Is Nothing Then
			Set myFirstResult = myResult
			Do
				intCountItem = intCountItem + 1
				curTotalAmount = curTotalAmount + myResult.Offset(0, 1).Value
				Set myResult = .FindNext(myResult)
			Loop While Not myResult Is Nothing And myResult.Address <> myFirstResult.Address
		End If
	End With
 
	MsgBox "Count of - " & strAccountNumber & " <Records> " & intCountItem & " <Total Amount> " & curTotalAmount
 
End Sub

The result of this Macro is shown here, listing the number of records found in the search and the total of the amounts found for the account number.

This Macro is performed in a Procedure and needs to be supplied with a value to be searched for in the range. The Find and FindNext functions are very similar to those available on a worksheet.

In the next post on Find and FindNext we will show how we can use Find to drive a Function that can be included to a worksheet. 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.

One comment

  1. Fantastic post. People should read this.