Code: Select all
Option Explicit
Function dXYinterp(dXin As Double, rX As Range, rY As Range) As Double
Dim dY As Double
Dim dSlp As Double
Dim dXfind As Double
Dim dXmin As Double
Dim l As Long
Dim lCNT As Long
Dim vXarr As Variant
Dim vYarr As Variant
'
lCNT = Application.Count(rX)
vXarr = rX.Parent.Range(rX.Cells(1, 1), rX.Cells(lCNT, 1)).Value2
vYarr = rY.Parent.Range(rY.Cells(1, 1), rY.Cells(lCNT, 1)).Value2
'
'
If dXin < CDbl(vXarr(1, 1)) Then
l = 1
Else
l = Application.Match(dXin, vXarr, 1)
If l = lCNT Then l = l - 1
End If
dSlp = vXarr(l + 1, 1) - vXarr(l, 1)
dSlp = (vYarr(l + 1, 1) - vYarr(l, 1)) / dSlp
dY = CDbl(vYarr(l, 1)) + (dXin - vXarr(l, 1)) * dSlp
dXYinterp = dY
'
End Function