I must be tired, because basic Math isn't working.....
 

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

I must be tired, because basic Math isn't working.....
 

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