Request help with Arithmentic UDF

[Dylan]

Please tell me why this isn't working.

Function SumInvoice(myC As Range) As Long
Dim Est As Long
Dim Fore As Long
Dim Prev As Long

Est = myC.Offset(0, 3).Value + Cells("K134")
Fore = myC.Offset(0, 4).Value + Cells("K135")
Prev = myC.Offset(0, 5).Value + Cells("K136")

If myC.Cells.Text < "Z*" Then
SumInvoice = 0

ElseIf Est > 0 Or Fore > 0 Then
SumInvoice = (Est + Fore) - Prev

Else: SumInvoice = 0

End If
End Function

 

[Mike H]

Maybe

Function SumInvoice(myC As Range) As Long
Dim Est As Long
Dim Fore As Long
Dim Prev As Long
Est = myC.Offset(0, 3).Value + Range("K134").Value
Fore = myC.Offset(0, 4).Value + Range("K135").Value
Prev = myC.Offset(0, 5).Value + Range("K136").Value
If myC.Cells.Text < "Z*" Then
SumInvoice = 0
ElseIf Est > 0 Or Fore > 0 Then
SumInvoice = (Est + Fore) - Prev
Else: SumInvoice = 0
End If
End Function

Mike

 

[Dylan]

Thanks Mike. Still doesn't work though.

=IF(B128>"Z*",0,SUM(SUMIF((F128,F270),F128>0,(F2,F270)),(SUMIF((G128,G270),G128>0)),(SUMIF((H128,H270),H128>0))))

I can't even get this function to work it out either. Show's how much of a
novice I am. I figure if I can get the function to work I can convert it into
a UDF.

 

[Dylan]

Hi Mike,

I managed to get a solution for the function as follows, but I would like to
convert it to a UDF.

=IF(ISTEXT(B128),0,IF(F2>0,F2+F128)+IF(G2>0,G2+G128)+IF(H2>0,H2+H128))

Solution provided by Sandy Mann at
http://www.microsoft.com/communitie...73e-d79d-4720-8866-0da784ce979c&lang=en&cr=us