CODEWITHSUNDEEP
excelvba
Geoff Scott
Geoff Scott

Reputation: 9

Find all occurrences of a specified number and sum a range of cells

I need to find all occurrences of a specific number in a spreadsheet, then sum the 5 consecutive numbers which appear 4 columns away from that specified number.

The specified number can repeat any number of times.

Example:
The number 132545 appears in 2 places, cells A7 and AB22.
I need to sum cells E7:I7 and AF22:AJ22 and store the grand total in a variable.

Upvotes: 0

Views: 102

Answers (1)

VBasic2008
VBasic2008

Reputation: 54777

Get Total From Offsetted Cells

  • The result for the data in the image is 113.

enter image description here

Option Explicit

Sub GetTotalTest()

    Dim ws As Worksheet: Set ws = ActiveSheet ' improve!
    If ws.AutoFilterMode Then ws.AutoFilterMode = False 
    
    Dim rg As Range: Set rg = ws.UsedRange
    
    Dim Total As Double: Total = GetTotal(rg, 4, 5, 132545)
    
    MsgBox Total, vbInformation

End Sub

Function GetTotal( _
    ByVal rg As Range, _
    ByVal ColumnOffset As Long, _
    ByVal CellsCount As Long, _
    ByVal Criteria As Variant) _
As Double
    
    Dim sCell As Range: Set sCell = rg.Find(Criteria, , xlFormulas, xlWhole)
    
    If sCell Is Nothing Then Exit Function
        
    Dim sFirstAddress As String: sFirstAddress = sCell.Address
    
    Dim cData() As Variant
    Dim cValue As Variant
    Dim c As Long
    Dim Total As Double
    
    Do
        cData = sCell.Offset(, ColumnOffset).Resize(, CellsCount).Value
        For c = 1 To CellsCount
            cValue = cData(1, c)
            If VarType(cValue) = vbDouble Then ' is a number
                Total = Total + cValue
            End If
        Next c
        Set sCell = rg.FindNext(sCell)
    Loop Until sCell.Address = sFirstAddress
        
    GetTotal = Total
    
End Function

Upvotes: 1

Related Questions