sum of absolute values with VBA

vegasguy

New Member
Joined
Feb 26, 2016
Messages
6
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.
 
If you mean you would like to SUM the numbers' absolute values, maybe:
Excel Workbook
CD
224
350
4100
537
621
736
8-18
967
109
1192
12-13
1319
1448
1528
16-18
1785
18-9
1933
20
21Sum (non ABS)591
22Sum (ABS)707
Sheet2
Excel 2010
Cell Formulas
RangeFormula
D21=SUM(D2:D19)
D22=SUMPRODUCT(ABS(D2:D19))


Hope that helps,

Mark
 
Upvote 0
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 & "))")
 
Upvote 0
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")
 
Upvote 0
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.
 
Upvote 0
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?
 
Last edited:
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top