Need help : VBA to calculate values with condition

[rxk]

Hi All,

HAPPY NEW YEAR!!!

I need some assistance how to make VBA. Sum range K4:X4 if corresponding cell of range K2:X2 is true (checkbox value).

I have formulas at range B4:B61. See attached workbook.

 

[vletm]

rxk
Test this by press Your [ Calc A ]-button.
You'll get those sums as Comments.
( I didn't want to delete Your ... formula.
If those values are as You needed then modify code (there is needed line).
but if You still would like to use 'formula' then test something like next to cell B4:
=SUMIFS(Table_Jan18[@[ARR1]:[BAC]],K$2:X$2,TRUE)
it'll be shorter than Your formula.

 

[rxk]

Hi vletm,

Your code is what I need, just a minor addition to get dynamically the last column and row.
For b = 4 To 61 <-- can be set to last column?
For x = 11 To 24 <-- can be set to last row?
Thank very much.
rxk

---
I found the code to fine the last row and column:

[/COLOR][/COLOR]
[COLOR=#ff0000][COLOR=#000000]'Finds the last non-blank row on a sheet/range.
  lRow = Cells.Find(What:="*", _
  After:=Range("A1"), _
  LookAt:=xlPart, _
  LookIn:=xlFormulas, _
  SearchOrder:=xlByRows, _
  SearchDirection:=xlPrevious, _
  MatchCase:=False).Row

'Finds the last non-blank row on a sheet/range.
  lCol = Cells.Find(What:="*", _
  After:=Range("A1"), _
  LookAt:=xlPart, _
  LookIn:=xlFormulas, _
  SearchOrder:=xlByColumns, _
  SearchDirection:=xlPrevious, _
  MatchCase:=False).Column

 

[vletm]

rxk
hmm ... dynamic ... did Your original case has that term?
Of course, many things can do ...
but I won't use those ..
I would use these...

    With ActiveSheet
        b_max = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        x_max = .UsedRange.Columns(.UsedRange.Columns.Count).Column
        For b = 4 To b_max
            sb = Empty
            For x = 11 To x_max
                If .Cells(2, x) Then sb = sb + .Cells(b, x)
            Next x
            .Cells(b, 2) = sb
        Next b
    End With

... and that x-loop no need

    With ActiveSheet
        b_max = .UsedRange.Rows(.UsedRange.Rows.Count).Row
        For b = 4 To b_max
            .Cells(b, 2) = WorksheetFunction.SumIfs(.Range("K" & b & ":X" & b), .Range("K2:X2"), True)
        Next b
    End With

 

[rxk]

Thank you vletm,

I am not good in VBA, just browsing some sample vba codes and tweak to suit my needs.

Your help is much appreciated.