Snasui Excel Forum

Code: Select all

Sub test0()
    Dim rAll As Range, r As Range
    Dim a() As String, i As Integer
    With Sheets("IA2")
        Set rAll = .Range("A16", .Range("A16").End(xlDown).Offset(-1, 0))
        For Each r In rAll
            If InStr(r, "(ACCT") > 0 Then
                ReDim Preserve a(i)
                a(i) = Replace(r.Offset(0, 3).Address, "$", "")
                i = i + 1
            End If
        Next r
        With .Range("a16").End(xlDown).Offset(0, 3)
            .Formula = "=Sum(" & Join(a,",") & ")"
            .Resize(1, 7).FillRight
        End With
    End With
End Sub

Code: Select all

Sub test0()
    Dim rCond As Range
    Dim rCriterea As String
    Dim rSum As Range
    With Sheets("IA2")
        Set rCond = .Range("A16", Range("A16").End(xlDown).Offset(-1, 0))
        rCriterea = "*(ACCT*"
        Set rSum = rCond.Offset(0, 3)
        .Range("a16").End(xlDown).Offset(0, 3).Value = Application.SumIf( _
            rCond, rCriterea, rSum)
    End With
End Sub