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

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" wrote:

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

Thanks Mike. Still doesn't work though.

=IF(B128"Z*",0,SUM(SUMIF((F128,F270),F1280,(F2,F 270)),(SUMIF((G128,G270),G1280)),(SUMIF((H128,H27 0),H1280))))

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.



"Mike H" wrote:

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" wrote:

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

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(F20,F2+F128)+IF(G20,G2+G12 8)+IF(H20,H2+H128))

Solution provided by Sandy Mann at
http://www.microsoft.com/communities...&lang=en&cr=us

"Dylan" wrote:

Thanks Mike. Still doesn't work though.

=IF(B128"Z*",0,SUM(SUMIF((F128,F270),F1280,(F2,F 270)),(SUMIF((G128,G270),G1280)),(SUMIF((H128,H27 0),H1280))))

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.



"Mike H" wrote:

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" wrote:

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