CODEWITHSUNDEEP
excelvba
Mark Austin
Mark Austin

Reputation: 129

Excel VBA Modify Existing Equation

I am trying to figure out how to modify an existing equation in VBA. The current equation is sum(A1+A3+A5) I am skipping every other row. I have a macro to add two rows at the end of the column A so when those new rows are added i need to update that equation to sum(A1+A3+A5+A7).

Upvotes: 2

Views: 12921

Answers (2)

chris neilsen
chris neilsen

Reputation: 53126

A better approach may be to use a different formula.

Eg, to sum all Odd rows in column A, use

=SUMPRODUCT(A:A*ISODD(ROW(A:A)))

Upvotes: 2

Andy G
Andy G

Reputation: 19367

if the formula is changed to =A1+A3+A5 then code could be:

Range("B1").Formula = Range("B1").Formula & "+" & Range("A7").Address

or, without dollar signs,

Range("B1").Formula = Range("B1").Formula & "+" & Range("A7").Address(False, False)

Added in response to OPs comment. If I assume the new cell is two rows above where the formula is, which is the currently the active cell:

ActiveCell.Formula = Replace(ActiveCell.Formula, ")", "," & _
    ActiveCell.Offset(-2, 0).Address(False, False) & ")")

Adjust the value -2 as necessary.

Upvotes: 1

Related Questions