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

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

"[email protected]" 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
>
>