CODEWITHSUNDEEP
excelvba
smz
smz

Reputation: 173

How dynamically to change the formula of a cell

I am trying to change the formula of the total grade of a student. There are two columns initially and if an add button is clicked, a new column is added also, and therefore the formula should be changed.

enter image description here

My formula in Excel is =SUM(J7, H7), that is to add only the percent.

My problem is, I don't know how to reset the formula and change to

       =SUM(J7,H7,F7,D7)/[no. of items added after clicking the add button]

Is there any way to solve this?

Note: Columns are alternate and not in series.

Upvotes: 1

Views: 132

Answers (2)

user4039065
user4039065

Reputation:

The key to this is your merged cell in C5:J5. This has a Range.MergeArea property that defines the entire area to be considered. The Intersect method can be used with a large number of even numbered columns to see which fit inside the MergeArea. Continuing to Intersect against the row involved will produce the Range.Address property of the correct SUM range.

It's a little unclear to me where this SUM function goes. The numbers in your sample do not indicate any obvious location. This uses column K and the row number is fed in as a parameter.

Sub main()
    new_formula rw:=7
    new_formula rw:=8
End Sub

Sub new_formula(rw As Long)
    Dim rng As Range, summative As Range
    With Worksheets("Sheet5")
        Set summative = .Rows(5).Find(what:="Summative", LookIn:=xlFormulas, LookAt:=xlWhole)
        Set rng = Intersect(summative.MergeArea.EntireColumn, _
                            .Rows(rw), _
                            Union(.Columns(4), .Columns(6), .Columns(8), .Columns(10), _
                                  .Columns(12), .Columns(14), .Columns(16), .Columns(18)))
        With summative.MergeArea
            .Cells(.Count).Offset(rw - .Row, 1).Formula = _
                "=sum(" & rng.Address(0, 0) & ")"
        End With
    End With
End Sub

        summative
            After running main.

Calling the sub from main sets a new sum formula first in row 7 and then in row 8. Adding or removing columns would require rerunning the sub but it seems likely that your columns are added by code and this could a part of that larger routine.

Upvotes: 0

GSerg
GSerg

Reputation: 78134

Only count odd columns if your formula:

=SUM(IF(MOD(COLUMN(K7)-COLUMN(C7:J7),2)=1,C7:J7,0))

That way it will automatically resize when you add columns (provided you add them by actually inserting columns).

K7 is the cell with the formula, C7:J7 is all cells of the data line.

Ctrl+Shift+Enter to enter.

Upvotes: 2

Related Questions