ignore summing values for each item contain shape(oval) for cells
hi
I would sum the all values from C: N for each item doesn't contain shape(oval) . the result should be in column O with considering will increase data by adding new rows. as in the result in column O when summing values will exclude any cells contains shape (OVAL) .
thanks
According to your attachment a VBA demonstration for starters :
PHP Code:
Sub Demo1() Dim A$(), L&, T(), V, W, C% With ActiveSheet.Shapes ReDim A(1 To .Count) For L = 1 To .Count With Range(.Item(L).TopLeftCell, .Item(L).BottomRightCell).Cells A(L) = .Item(2 + (.Count < 3)).Address End With Next End With ReDim T(6 To Cells(Rows.Count, 1).End(xlUp).Row, 0) For L = 6 To UBound(T) V = Evaluate(Replace("IF({1},ADDRESS(ROW(C#:N#),COLUMN(C#:N#)))", "#", L)) W = Application.Match(V, A, 0) For C = 1 To 12 If IsNumeric(W(C)) Then V(C) = False Next T(L, 0) = Application.Sum(Range(Join(Filter(V, False, False), ","))) Next Range("O6:O" & UBound(T)) = T End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Sub Demo1v() Dim A$(), L&, T(), Rc As Range With ActiveSheet.Shapes ReDim A(1 To .Count) For L = 1 To .Count With Range(.Item(L).TopLeftCell, .Item(L).BottomRightCell).Cells A(L) = .Item(2 + (.Count < 3)).Address End With Next End With ReDim T(6 To Cells(Rows.Count, 1).End(xlUp).Row, 0) For Each Rc In Range("C6:N" & UBound(T)) If IsError(Application.Match(Rc.Address, A, 0)) Then T(Rc.Row, 0) = T(Rc.Row, 0) + Rc Next Range("O6:O" & UBound(T)) = T End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 06-11-2022 at 10:28 AM.
Reason: optimization …
Bookmarks