Consulting

Results 1 to 2 of 2

Thread: Sumif macro in Excel for SAPBW Bex reports

  1. #1

    Sumif macro in Excel for SAPBW Bex reports

    Hi,

    I need to add a list of values in a BW SAP BEx report where the number of rows changes evertime the report is run (for a different period).
    The values that needs summing, are SHOWN as positives in the report, but the actual value (positive or negative) depends on the cell value in another column. A denotes negative value, B denotes positive value.

    Column 1 Report Value Actual Value (not shown in report)
    A 15 -15
    B 5 5
    B 2 2
    A 7 -7
    B 8 8
    A 19 -19
    summation 56 -26

    How do I go about entering a macro in the spreadsheet where the SUM for the Report Value column will be the absolute value of 26 and which can be used independent of the number of rows in the report?

    Any help is very much appreciated!

    Thanks

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    I'd use as user defined function (UDF) since IMHO they're more flexible for things like that


    [vba]
    Option Explicit

    Function AbsoluteSum(PositiveNegativeRange As Range, ValuesRange As Range) As Variant
    Dim aryPN As Variant, aryValue As Variant
    Dim i As Long

    aryPN = PositiveNegativeRange.Columns(1).Value
    aryValue = ValuesRange.Columns(1).Value

    If UBound(aryPN) <> UBound(aryValue) Then
    AbsoluteSum = CVErr(xlErrNum)
    Exit Function
    End If

    For i = LBound(aryPN, 1) To UBound(aryPN, 1)
    If UCase(aryPN(i, 1)) = "A" Then
    If IsNumeric(aryValue(i, 1)) Then
    aryValue(i, 1) = -1# * aryValue(i, 1)
    Else
    aryValue(i, 1) = 0#
    End If

    ElseIf UCase(aryPN(i, 1)) <> "B" Then
    AbsoluteSum = CVErr(xlErrNA)
    Exit Function
    End If
    Next i
    AbsoluteSum = Application.WorksheetFunction.Sum(aryValue)
    End Function
    [/vba]

    Paul
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •