Help with UDF - will not sum values in range when comparing.

[jbrouse]

Good morning, everyone.

Can someone please help me with my UDF below. If I remove the code comparing to MY_CELL, it will sum up everything in theRange, but no matter how I do it, I can't get it to sum up only if the value in Column D of that row is equal to MY_CELL. It goes to FuncFail and gives me MY_CELL as the result.

Function FG(theRange As Range)
    
    Dim vArr As Variant
    Dim v As Variant
    Dim r As Double
    Dim MY_CELL
    Dim d As Double
    
    On Error GoTo FuncFail
    r = 0
    
    vArr = theRange.Value2
    MY_CELL = Cells(Application.Caller.Row, 4) & Cells(Application.Caller.Row, 5) & Cells(Application.Caller.Row, 6)
    For Each v In vArr
        d = CDbl(v)
        If Application.WorksheetFunction.IsNumber(v) Then
            If Cells(v.Row, 4) = MY_CELL The
                r = r + d
            End If
        End If
    Next v            

    FG = r
    Exit Function
    
FuncFail:
    
    FG = MY_CELL

End Function

Any other suggestions would be great as well. I do know that using Application.Caller is supposed to not be very efficient. Thanks in advance!

 

[RoryA]

If you are not passing all the relevant cells as arguments to your function, then you ought to make the function volatile or it won't update properly. And if you have a lot of these functions, making it volatile is going to make your spreadsheet crawl like an asthmatic snail.

 

[jbrouse]

I understand what you are saying.

I originally pulled all of my cell arguments as references and had a big nasty For i =, If Then statement that did work. Unfortunately, it took minutes to calculate and in my research to find a faster, more efficient way of doing it, I stumbled across some websites that recommended doing it the way I am. I just can't figure out the correct syntax. The UDF is far from complete, I'm just taking it a step at a time.

As I mentioned before, ultimately, I am not the one who will be having to manipulate the formulas. If the next Sales Plan I get has 10000 rows instead of 500? It's easy for me to go in and quickly change all the formulas to instead look for 10000 rows, but other users might not be able to do it.

Thanks for the help though. I do appreciate it.

 

[RoryA]

You should look at dynamic named ranges then, so the formulas will adjust as the data increases. As a general rule, native Excel formulas will be faster than UDFs.

 

[kpark91]

Ok. So let me get this straight.

In Sheet2 you have column F and G
F - reference numbers
G - volumes

and in sheet1
D - partial ref
E - partial ref
F - partial ref

Now, you want to use the formulas like FG(G2:G500) to get the sum:
sum where you will add the volumes provided that sheet2's references exist in sheet1's references.

Is this correct?

so many verification... I just like to write the code at one go instead of tweaking it everytime (not a good programming practice )

 

[jbrouse]

That sounds correct to me, yes.

Thanks!

 

[kpark91]

You should look at dynamic named ranges then, so the formulas will adjust as the data increases. As a general rule, native Excel formulas will be faster than UDFs.

@jbrouse, have you tried his method?
His sounds much faster and better and easier to maintain for sure.

 

[jbrouse]

I have used them before, but for the life of me cannot remember how. I'll look into it.

Thanks, everyone!

 

[jbrouse]

OK, so I guess I need a little more help, and I thank you all for being patient with me...

Going back to the original formula that I had, using SUMPRODUCT

SUMPRODUCT(--('Sales Plan'!$F$2:$F$1004='2011'!$A8),--(LEFT('Sales Plan'!$D$2:$D$1004,12)='2011'!$D8),--('Sales Plan'!$A$2:$A$1004='2011'!$O$5),'Sales Plan'!$G$2:$G$1004)

This compares "Department" (column F) in Sales Plan to the "Department" (Column A) of the row that the formula is in. It also compares the left 12 digits of the part no. in Sales Plan, Column D to the partial part no. in Column D of the row that the formula is in. It sums all volumes in Sales Plan, Column G that meet both criteria.

This formula is currently in Column P on my sheet '2011'. I need the same formula in every 3rd column (P, U, X, ..., AY), but need the column that it references to increment by one; that is,

Column P - 'Sales Plan'!$G$2:$G$1004
Column U - 'Sales Plan'!$H$2:$H$1004
Column X - 'Sales Plan'!$I$2:$I$1004
and so on...

The sheet is currently 790 rows by 12 columns. It takes more than 30s to calculate.

Can someone help me clean it up so that the formula doesn't scare off other people who may have to use it and that can be adapted to use in any column?

Thanks!