I'm trying to interpolate in a 1-D column of numbers using code downloaded from "http://www.tushar-mehta.com/excel/newsgroups/interpolation/#Introduction_". It's working fine apart from one line in a function. The problem line is:

code
If MatchVal = XVals.Cells.Count _
And RealEqual(TargetVal, .Index(XVals, MatchVal)) Then
/code

The error message I'm getting is "Object required(Number= 424)", but I just can't work out why it's producing this error.

The relevant code is reproduced below. Any help/advice would be much appreciated.

Code extracted from UserForm module:
code
'Name and dimension X and Y arrays to be used in TVD interpolation
Dim LastRow As Integer, XVals(12) As Variant, YVals(12) As Variant, _
i As Integer, j As Integer

LastRow = Worksheets("Calculations1").Cells(Rows.Count, "H").End(xlUp).Row
For i = 3 To LastRow
XVals(i - 3) = Worksheets("Calculations1").Range("H" & i)
YVals(i - 3) = Worksheets("Calculations1").Range("I" & i)
Next i

'Create MD column and calculate interpolated TVD values; hence
'create TVD column
Dim MDmax As Variant, MDinc As Variant, MDvalue(20) As Variant, _
TVDvalue(20) As Variant

Sheets("Calculations3").Cells.Range("A7").Value = "Surface"

MDmax = Sheets("Calculations1").Range("B9").Value
MDinc = MDmax / 20

With Sheets("Calculations3")
MDvalue(0) = 0
.Range("B7").FormulaArray = MDvalue(0)
For i = 1 To 20
MDvalue(i) = MDvalue(i - 1) + MDinc
.Range("B" & i + 7).FormulaArray = MDvalue(i)
Next i
TVDvalue(0) = 0
.Range("C7").FormulaArray = TVDvalue(0)
For i = 1 To 20
TVDvalue(i) = LinearInterp(XVals(), YVals(), MDvalue(i))
.Range("C" & i + 7).FormulaArray = TVDvalue(i)
Next i
End With
/code

.. and the related function in a standard module:

code
Option Explicit
Option Compare Text

Function RealEqual(X, Y) As Boolean
RealEqual = Abs(X - Y) <= 0.00000001
End Function

Function LinearInterp(XVals, YVals, TargetVal)
Dim MatchVal

On Error GoTo ErrXit

With Application.WorksheetFunction
MatchVal = .Match(TargetVal, XVals, 1)
If MatchVal = XVals.Cells.Count _
And RealEqual(TargetVal, .Index(XVals, MatchVal)) Then
LinearInterp = .Index(YVals, MatchVal)
Else
LinearInterp = .Index(YVals, MatchVal) _
+ (.Index(YVals, MatchVal + 1) - .Index(YVals, MatchVal)) _
/ (.Index(XVals, MatchVal + 1) _
- .Index(XVals, MatchVal)) _
* (TargetVal - .Index(XVals, MatchVal))
End If
End With
Exit Function

ErrXit:
With Err
LinearInterp = .Description & "(Number= " & .Number & ")"
End With

End Function
/code

That 424 error is usually due to an invalid object qualifier, or an unrecognizable object, or a missing Set statement. On an unrelated caveat, the LastRow variable is of the Integer type, but if your column of data exceeds 32,767 records, the Long type would be required.

Looking at your post and this code for the first time, it's not clear what code is Tushar's and what might be yours, if any, that raised the 424. My first observation is that the TargetVal variable may not have not been defined by some value for which a calculation can be made.

Otherwise, by chance, if the error occurred on code that is not yours, have you reached out to Tushar, to ask him if he's encountered this same issue? Tushar is another Microsoft Excel MVP; his contact info is found on his website where you downloaded his code from. Conversely, should the error have occurred on any additions to the code that you made, check to verify that your spelling and declaration(s) are correct of the object(s) you appended the code with.