Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 893
  • 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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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