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!

 

[Weaver]

If Cells(v.Row, 4) = MY_CELL The

Missing letter 'n'?

 

[jbrouse]

hmmm, it's correct in my code. Don't know how I lost my 'n' when I posted.

 

[jbrouse]

Still can't get it to work.

Can I not reference cells that aren't in the defined range of the function? It definitely seems to have a problem any time I reference something other than the specified range (=FG('Sheet2'!$G$2:$G$500))

Thanks.

 

[jasonb75]

Looks like you have a few declaration errors, it would be easier to find a solution if we knew what you were trying to do with the UDF.

This is an untested edit done in the reply box based on what I think you're trying to do.

Function FG(theRange As Range) As Double
    
    Dim v As Range
    Dim r As Double
    Dim MY_CELL As Double
    Dim d As Double
    
    On Error GoTo FuncFail
    r = 0
    
    MY_CELL = CDbl(Cells(Application.Caller.Row, 4) & Cells(Application.Caller.Row, 5) & "." & Cells(Application.Caller.Row, 6))
    For Each v In theRange
        
    d = CDbl(v)
        If IsNumeric(v) Then
            If Cells(v.Row, 4) = MY_CELL Then
                r = r + d
            End If
        End If
    Next v
    FG = r
    Exit Function
    
FuncFail:
    
    FG = MY_CELL
End Function

 

[jbrouse]

Column D in Sheet1 has partial part nos. I have removed the revision level, which is in Column E, and location code, which is in Column F. So, a full part no. that is EE123456-7890AA, will have EE123456-789 in Column D, 0 in Column E, and AA in Column F.

Column D in Sheet2 has complete part nos. Each part no. can occur more than once.
Column G in Sheet2 has volumes.

The function, as written - =FG($G$2:$G$500) - should sum the volumes in column G only if the full part no. from Sheet1 matches the full part no. from Sheet1, which is why I use MY_CELL to basically recreate the full part no.

Thanks for your help!

 

[jasonb75]

Does it need to be a UDF or would you be happy with a formula?

If I read that correcly then this should give the right results.

=SUMIF(Sheet2!$D$2:$D$500,Sheet1!$D2&Sheet1!$E2&"*",Sheet2!$G$2:$G$500)

 

[kpark91]

The function, as written - =FG($G$2:$G$500) - should sum the volumes in column G only if the full part no. from Sheet1 matches the full part no. from Sheet1, which is why I use MY_CELL to basically recreate the full part no.

do you mean from Sheet2 matches the full part no. from Sheet1 (sheet2!F & i == sheet1!F & j)?

Why would you need to check if full part no. from Sheet2 matches full part no. from Sheet1 if you have 'copied' the part no. from Sheet1 to Sheet2 in the first place?

 

[jbrouse]

There's actually more to it. I have been using SUMPRODUCT() with multiple criteria and having to use multiple versions to handle all of the different conditions that I have. The worksheet I am dealing with is actually thousands of lines long and many columns wide. I have been struggling with getting the right formula into the right cells, so I was going to attempt to make a UDF that would be much easier to look at and follow.

Ultimately, I am hoping that I can hand this workbook to someone else and they can follow it.

I just figured having something like =FG($G$2:$G$500) and letting my UDF handle all the inner workings was easier to follow than

=IF($C20<>0,SUMPRODUCT(--('Sales Plan'!$F$2:$F$1004='2011'!$A20),--(LEFT('Sales Plan'!$D$2:$D$1004,12)='2011'!$J20),--('Sales Plan'!$A$2:$A$1004='2011'!$O$5),'Sales Plan'!$G$2:$G$1004),SUMPRODUCT(--('Sales Plan'!$F$2:$F$1004='2011'!$A20),--(LEFT('Sales Plan'!$D$2:$D$1004,12)='2011'!$D20),--('Sales Plan'!$A$2:$A$1004='2011'!$O$5),'Sales Plan'!$G$2:$G$1004))

I just need to figure out how to reference cells outside of the listed range. Clearly it can be done because my MY_CELL = actually pulls the correct part no.

Thanks!

 

[jbrouse]

Why would you need to check if full part no. from Sheet2 matches full part no. from Sheet1 if you have 'copied' the part no. from Sheet1 to Sheet2 in the first place?

It is not a copy of the Sheet2. Sheet1 is my master list of part nos. Sheet2 is a Sales Plan that I get from our customer that contains multiple instances of each part no. So instead of having on Sheet2

Part1 - 20000
Part1 - 20000
Part1 - 20000
Part1 - 20000

Sheet1 simply has

Part1 - 80000