Sorry for cross post, initial post was probably wrong group...

Cell D3 = value
Cell D4 = value D3
Range C3:C20 and B3:B20 have values in them
Can someone tell me why the Case 1 gives back a #Value!

Thanks in advance - Marston

Function FIFO(Current, Prior, Source As Range,Compare As Range)
Dim A As Integer, B As Integer, C As Integer
Dim rng1 As Range, rng2 As Range
Set rng1 = Source
Set rng2 = Compare
A = Application.WorksheetFunction.Match(Current,Source ,1)
B = Application.WorksheetFunction.Match(Prior,Source,1 )
C = A - B
Select Case C
Case 0
FIFO = rng2.Offset(A,0)
Case 1
E = rng1.Offset(A-1,0)
F = rng2.Offset(A,0)
G = rng2.Offset(B,0)
FIFO = ((Current - E)*F +(E - Prior)*G)/(Current - Prior)
Case Else
FIFO = 0
End Select
End Function

Could you provide some data for Current, Prior, Source, and Compare. Without
knowing what the function is looking at it's hard to determine the problem.

Mike

" wrote:

Sorry for cross post, initial post was probably wrong group...

Cell D3 = value
Cell D4 = value D3
Range C3:C20 and B3:B20 have values in them
Can someone tell me why the Case 1 gives back a #Value!

Thanks in advance - Marston

Function FIFO(Current, Prior, Source As Range,Compare As Range)
Dim A As Integer, B As Integer, C As Integer
Dim rng1 As Range, rng2 As Range
Set rng1 = Source
Set rng2 = Compare
A = Application.WorksheetFunction.Match(Current,Source ,1)
B = Application.WorksheetFunction.Match(Prior,Source,1 )
C = A - B
Select Case C
Case 0
FIFO = rng2.Offset(A,0)
Case 1
E = rng1.Offset(A-1,0)
F = rng2.Offset(A,0)
G = rng2.Offset(B,0)
FIFO = ((Current - E)*F +(E - Prior)*G)/(Current - Prior)
Case Else
FIFO = 0
End Select
End Function