VBA to sum all positive values in column, and all negative

[espenskeie]

Hello

I have a column with some positive and some negative values, and also some empty cells.

I try to figure out how to write a VBA code for this, and would prefer to avoid .formula codes if possible...

This is what I have been messing with so far, completely without success:

Sub TEst(rRange As Range)
rRange As Double
Application.Volatile True
 Dim rSumRange As Range
 Dim tosum As Long
 
 
        Set rSumRange = .Range("AD2", .Cells(Rows.count, 31).End(xlUp))
        
        For Each rSumRange In rRange
        With rSumRange
        If IsNumeric(.Value) Then
            If .Value > 0 Then
                tosum = tosum + .Value
            End If
        End If
        End With
        Next rSumRange
        .Range("AD" & lrAna).Value = tosum
End Sub

Regards
Espen

 

[circledchicken]

Hi Espen,

I think it might be easiest to use SUMIF here e.g. for the positive numbers:

Excel Workbook
	A	B
1	1	6
2	2
3	3
4	-1
5	-2
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B1	=SUMIF(A1:A5,">0")

And the equivalent VBA code would be something like this (I have used a message box just as a demonstration):

MsgBox WorksheetFunction.SumIf(rSumRange, ">0")

 

[Scott Huish]

You don't need .Formula, but the worksheet function SUMIF can already do this, which you can use in your VBA code. Do you want to avoid this as well?

 

[espenskeie]

I was most worried about using .formula functions because the data they will refer to will change every now and then, and then those values will change too.

But I guess I can solve that by running a copy of each section after its done, and paste special with paste values?

Regards
Espen

 

[Scott Huish]

Here's a way without using worksheet functions:

Sub test()
Dim x As Long, c As Range, positiveSum As Double, negativeSum As Double
x = Range("AD" & Rows.Count).End(xlUp).Row
For Each c In Range("AD2:AD" & x)
    If IsNumeric(c) Then
        If Sgn(c) = 1 Then positiveSum = positiveSum + c
        If Sgn(c) = -1 Then negativeSum = negativeSum + c
    End If
Next
Range("AD" & x + 1) = "Positive: " & positiveSum
Range("AD" & x + 2) = "Negative: " & negativeSum
End Sub

 

[Scott Huish]

You don't need .Formula to work with worksheet functions in VBA. (Note, not all Excel functions are available this way)

Here is the same thing using worksheet functions.

Sub test()
Dim c As Range, positiveSum As Double, negativeSum As Double
Set c = Range("AD2:AD" & Range("AD" & Rows.Count).End(xlUp).Row)
positiveSum = Application.WorksheetFunction.SumIf(c, ">0")
negativeSum = Application.WorksheetFunction.SumIf(c, "<0")
c.Offset(2).Resize(1) = "Positive: " & positiveSum
c.Offset(3).Resize(1) = "Negative: " & negativeSum
End Sub

 

[espenskeie]

Thanks HOTPEPPER, the one without worksheet functions worked just perfect.

May I ask if it is difficult to add an extra criterea? Now it does sum based on positive or negative values. But if I like to have 4 groups, in the column besides, column AE, I have values like "Long" and "Short".

If thats possible I would save some space and maybe a code or two...

Regards
Espen

 

[Scott Huish]

You mean, you have in Column AD, the words Long and Short, and all positive values in Column AE?