• Status: Solved
• Priority: Medium
• Security: Public
• Views: 915

# Excel Formula writing with VBA

So lets say we have 10 rows of data on excel

-4
2
4
-5
1
5
-6
7
-9
2

On the 11th row I would like to write a formula (not just the value) which adds the positive value in the 10 rows above.
On the 12th row I woul like to write a formula which adds the negative value in the 10 rows above.

So something similar to

Sheets(a.Name).Range("T2").Offset(isbet + 3, 0).Value = "=Sum(T" & 2 & ":" & "T" & isbet + 1 & ")"

but instead of sum function it is supposed to be =E2+E5+E7....

The values in that 10 row is going to change everyday.
0
awesomejohn19
• 2
1 Solution

Commented:
This should be exactly what you need mate:
Sum Only Negative Numbers
0

Commented:
``````=SUM(IF(\$T\$2:\$T\$11>0,\$T\$2:\$T\$11,0))
=SUM(IF(\$T\$2:\$T\$11<0,\$T\$2:\$T\$11,0))
``````
Confirmed with ctrl-shift-enter as an array formula.

Are the formulas you need.  Do you want to enter these with VBA?  If so you need to use the FormulaArray  property.
0

Commented:
I do love array formulas but there is no need for that here this will do it:
=SUMIF(T2:T10,”<0¿)

or in VBA
Dim SumRange As Range
Set SumRange = Sheets(1).Range("T1")

With SumRange
.Formula = "=SUMIF(T2:T10,"<0",T2:T10)"
.Value = .Value
End With
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.