sum of absolute values with VBA

[vegasguy]

Hi all,

I would like to evaluate a range by taking the sum of absolute values. I'm kind of new to VBA and am stumped. I can take the sum of a range by using:

x = Application.Sum(Selection)

but I can't seem to get it to work with absolute values.

Does anyone have any suggestions?

Thanks from "but it's a dry heat" Las Vegas.

 

[GTO]

If you mean you would like to SUM the numbers' absolute values, maybe:

Excel Workbook
	C	D
2		24
3		50
4		100
5		37
6		21
7		36
8		-18
9		67
10		9
11		92
12		-13
13		19
14		48
15		28
16		-18
17		85
18		-9
19		33
20
21	Sum (non ABS)	591
22	Sum (ABS)	707
Sheet2

Excel 2010

Cell Formulas
Range	Formula
D21	=SUM(D2:D19)
D22	=SUMPRODUCT(ABS(D2:D19))

Hope that helps,

Mark

 

[Rick Rothstein]

Hi all,

I would like to evaluate a range by taking the sum of absolute values. I'm kind of new to VBA and am stumped. I can take the sum of a range by using:

x = Application.Sum(Selection)

but I can't seem to get it to work with absolute values.

Try it like this...

x = Evaluate("SUM(ABS(" & Selection.Address & "))")

 

[pgc01]

Hi
Welcome to the board

For a vba solution, since you don't have access to an ABS() method, you can use:

d = Application.SumIf(Selection, ">0") - Application.SumIf(Selection, "<0")

 

[vegasguy]

Try it like this...

x = Evaluate("SUM(ABS(" & Selection.Address & "))")

Thanks Rick but I get a "Run time error 13: Type mismatch" when I try that. The range I am evaluating contains formulas, text and numbers. Here is a more complete section of the macro:

Range(FirstDeptRow, NextDeptRow).Activate

If x = Evaluate("SUM(ABS(" & Selection.Address & "))") Then
Selection.EntireRow.Hidden = True

"FirstDeptRow" and "NextDeptRow" are variables populated earlier in the macro execution.

 

[Rick Rothstein]

Thanks Rick but I get a "Run time error 13: Type mismatch" when I try that

Do you Dim your variables? If so, what is the variable "x" Dim'med as?

The range I am evaluating contains formulas, text and numbers.

And you did not think that was important enough to mention in your original message? Maybe you should tell us how your data is laid out in the range.

"FirstDeptRow" and "NextDeptRow" are variables populated earlier in the macro execution.

Populated with what... row numbers? Is your range a set of entire rows?

 

[vegasguy]

Hi
Welcome to the board

For a vba solution, since you don't have access to an ABS() method, you can use:

d = Application.SumIf(Selection, ">0") - Application.SumIf(Selection, "<0")

Thanks that works!

 

[pgc01]

You're welcome.