Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > BOOK: Excel VBA 24-Hour Trainer 2nd edition
Password Reminder
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
BOOK: Excel VBA 24-Hour Trainer 2nd edition
This is the forum to discuss the Wrox book Excel VBA 24-Hour Trainer 2nd Edition by Tom Urtis; ISBN: 978-1-118-99137-4
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Excel VBA 24-Hour Trainer 2nd edition section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Display Modes
  #1 (permalink)  
Old March 10th, 2016, 10:37 AM
Authorized User
Points: 44, Level: 1
Points: 44, Level: 1 Points: 44, Level: 1 Points: 44, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Dec 2015
Location: Aberdeen, UK
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default Interpolation - Error related to counting cells in an aray

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:

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

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:
'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

.. and the related function in a standard module:

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)
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

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

End Function
Reply With Quote
  #2 (permalink)  
Old March 12th, 2016, 06:59 PM
Wrox Author
Points: 213, Level: 4
Points: 213, Level: 4 Points: 213, Level: 4 Points: 213, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
Join Date: Jan 2015
Location: San Francisco, California, USA
Posts: 34
Thanks: 0
Thanked 5 Times in 5 Posts

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.
Reply With Quote

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
VbA change colour cells based on cells values in other sheets Bruno Excel VBA 3 January 26th, 2016 04:52 PM
VbA change colour cells based on cells values in oother sheets Bruno Excel VBA 1 January 26th, 2016 07:14 AM
Converting empty cells in Excel to null cells for importing into Access rmccafferty Excel VBA 0 August 30th, 2012 03:07 AM
Counting the HTTP 500 error per minute spraveenitpro Apache Tomcat 0 February 23rd, 2010 02:51 AM
Error in Counting Jonas Access VBA 2 August 15th, 2006 12:21 AM

All times are GMT -4. The time now is 01:57 AM.

Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.