Sum/ABS

[NWE]

Hi,

I would like to sum the absolute value of a range and then subtract that range from another range on a different worksheet. I keep running into this error: "Sub or Function not defined". I think It is because I am declaring "a" as a double variable..but I want the resultant in that cell.

Here is what I got

Public Sub Calculate()
    Dim a As Range
    Dim x As Range
    Dim y As Range
    a = Worksheets("Generalized Report").Range("A4")
    x = Worksheets("Contract").Range("G2:G10000")
    y = Worksheets("As Built").Range("G2:G10000")
'Calculate the absolute values of the quantities
    a = Evaluate(Sum(Abs("y"))) - (Sum(Abs("x")))
End Sub

[Kenneth Hobs]

I don't know what the purpose of the first a is.

If you want to get the Abs() value for each cell in range x, then a custom formula would be needed. If you just wanted the Abs() of the sum of range x then:

Sub Calc1()    
    Dim a As Double
    Dim x As Range
    Dim y As Range
    
    a = Worksheets("Generalized Report").Range("A4")
    
    [Contract!G2] = 2
    [Contract!G3] = -4
    ['As Built'!G2] = 44
    ['As Built'!G3] = -4
    
    Set x = Worksheets("Contract").Range("G2:G10000")
    Set y = Worksheets("As Built").Range("G2:G10000")
'Calculate the absolute values of the quantities
    a = Abs(WorksheetFunction.Sum([y])) - _
      Abs(WorksheetFunction.Sum([x].Value))
    MsgBox a 'a=38
End Sub

[NWE]

Hi,

I want to get the absolute value of the sum of the range in y and then subtract that from the same from x

so: =SumAbsy-SumAbsX and then the resultant to print to that cell declared with a. I do not understand this section here:

[Contract!G2] = 2
    [Contract!G3] = -4
    ['As Built'!G2] = 44
    ['As Built'!G3] = -4

[Kenneth Hobs]

It is sample data for a blank workbook with those 3 worksheet names so that you can verify that the "math" is right. The absolute value of the sum of the range minus the other is 38 using the sample data. Delete those parts when you verify that it works as needed.

Normally, one uses the existing range from G2 down dynamically in the macro rather than hard coding a range.

Here is another example where no variables need to be set nor defined.

Sub Calc2()
  'For simple testing purposes: Sample data where result should be 38.
  [Contract!G2] = 2
  [Contract!G3] = -4
  ['As Built'!G2] = 44
  ['As Built'!G3] = -4


  'Put the result into worksheet "Generalized Report", cell A4.
  ['Generalized Report'!A4] = _
    Abs(WorksheetFunction.Sum(['As Built'!G2:G10000])) _
    - Abs(WorksheetFunction.Sum([Contract!G2:G10000]))
  
  'Show result for testing purposes.
  MsgBox ['Generalized Report'!A4] '=38 using sample test data.
End Sub

Of course if you do use a static range, a standard formula in A4 should suffice.

[NWE]

That sample data makes sense now. I made some adjustments to the code and got it to work for my purposes so I thank you for that!