Sleeper: VBA Function to total expense

[thepr1nter]

Here's a challenge for you.

I started using the following formula to total expense in two different worksheets based on values in certain fields but things got out of hand when I had too many criteria to test for:

=SUM(IF((?NPS Detail '!$B$19:$B$1000=$C4)*(('NPS Detail'!$P$19:$P$1000="22c")+('NPS Detail'!$P$19:$P$1000="25c")),'NPS Detail'!$V$19:$V$1000,0))+SUM(IF(('P-Card '!$B$9:$B$710=$C4)*(('P-Card '!$I$9:$I$710="22c")+('P-Card '!$I$9:$I$710="25c")),'P-Card '!$N$9:$N$710,0))

( this formula is entered using the {CTRL + Shift + CR} )

So I graduated to this next formula which was easier in some ways but it's not transportable to a new workbook (as the last one was not either):

=DSUM('NPS Detail'!$A$19:$W$1000,"Exp.",Criteria!$A$2:$P$36)+DSUM('P-Card '!$A$9:$W$710,"Exp.",Criteria!$AA$2:$AO$36)

This one is a simpler but the criteria list gets out of hand and when I transport it to a new workbook it carries all the references to the original workbook with it.

So my idea is to create a function that I believe would copy to another workbook with ease. But I'm having trouble figuring out how to read through the rows of the worksheet and compare particular fields with criteria to determine if I need to add that row to the sum. I also tried to use the DSUM function in my UDF with no success.

Can anyone help point me in the right direction?

[malik641]

You don't think you could post a workbook do you??

And Welcome to VBA Express!

[thepr1nter]

Well . . . guess I could. It was rather large so I eliminated a bunch of stuff and put in data that is only for example. But the structure of the sheet is sound and it will give you the idea of whats going on.

You'll see that the "Direct_cost" sheet is the one with the complicated formula. And the macro in Module 9 is the one I've been working on, that doesn't work. The idea is to total the expense based on the Account object code in two sheets (NPS Detail and Pcard) and the value in the category column which could be several for each cost center. (the criteria sheet holds this data)

Thanks for your help.

[thepr1nter]

Okay, so I figured out how to make it work, sort of.

Here's what I came up with:

Function Obj_sum(objcode As Object, costcenter As String) As Double
Application.Volatile
Dim NPS_Criteria As Range
    Dim Pcard_Criteria As Range
    Dim NPS_Data As Range
    Dim Pcard_Data As Range
    Dim Total As Double
Set NPS_Data = ThisWorkbook.Worksheets("NPS Detail").Range("A6:w700")
    Set Pcard_Data = ThisWorkbook.Worksheets("P-Card ").Range("A8:O708")
If (costcenter = "Admin" And objcode.Value = 714100) Then
        Set NPS_Criteria = ThisWorkbook.Worksheets("Criteria").Range("A37:R71")
        Set Pcard_Criteria = ThisWorkbook.Worksheets("Criteria").Range("AA37:AO71")
        Total = WorksheetFunction.DSum(NPS_Data, "Exp.", NPS_Criteria) + _
            WorksheetFunction.DSum(Pcard_Data, "Exp.", Pcard_Criteria)
    End If
Obj_sum = Total
End Function

The problem that I see is that with 14 costcenters and 38 objcodes, there has to be 532 tests (IF statements) written to satisfy every possibility or a total of 3192 lines of code.

There must be an easier way.

Maybe I need to start with a subroutine and a loop that writes the object code into a criteria range then performs the function and modifys a cell range for each object code since I can't call a subroutine within a function to perform that action.

Thoughts?

[thepr1nter]

Ahhh...HA . . . PivotTables must be the way to go!

What'da'ya think?