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

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
Asked:
awesomejohn19
  • 2
1 Solution
 
OCDanCommented:
This should be exactly what you need mate:
Sum Only Negative Numbers
0
 
andrewssd3Commented:
=SUM(IF($T$2:$T$11>0,$T$2:$T$11,0))
=SUM(IF($T$2:$T$11<0,$T$2:$T$11,0))

Open in new window

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
 
OCDanCommented:
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.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now