Find and FindNext

2
1737

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)