Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA 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
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 18th, 2006, 06:00 AM
Registered User
 
Join Date: Feb 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default Runtime Error 424: Object needed

Following Function give Run-time Error 424 Object needed in the line:LookupValue = Application.Caller.Offset(1, -1).Value.
Somebody knows what's wrong?
Formula gives back #value!. What I try to do is this: eg. when I insert the function in cel F4, the function matches the value in E5 with matrix.
Code:
Function LookOneBack() As Long
Dim LookupValue As Variant
Dim rngMatrix As Range

        Application.Volatile

        LookupValue = Application.Caller.Offset(1, -1).Value
        Set rngMatrix = ThisWorkbook.Worksheets(1).Range("B1:B4")

        If rngMatrix.Find(What:=LookupValue, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            MatchCase:=False) Is Nothing Then
            LookOneBack = 0
        Else
            LookOneBack = 2
        End If

        Set rngMatrix = Nothing
End Function


Reply With Quote
  #2 (permalink)  
Old February 18th, 2006, 10:18 AM
Registered User
 
Join Date: Feb 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Now I simplified the function and it works, it gives the right value back. Still when I debug the function it gives same Run-time Error 424 Object needed. Can anyone help me solve this bug?

Function LookBack() As Variant

Application.Volatile
LookBack = Application.Caller.Offset(1, -1).Value

End Function


Reply With Quote
  #3 (permalink)  
Old February 18th, 2006, 10:48 AM
Registered User
 
Join Date: Feb 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Somewhere I use a wrong data type, but where?

Reply With Quote
  #4 (permalink)  
Old February 20th, 2006, 04:50 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: London, , United Kingdom.
Posts: 173
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Not sure I can help you here. I've preped up your code with a bit of defensive programming and the code both evelautes as expected and debugs prefectly happily. One Q: Are you using Excel 2003? This is the version of Excel I tried the code on and different versions may respond differently.

Code:
Public Function LookOneBack() As String

    LookOneBack = ""

    Application.Volatile

    If TypeName(Application.Caller) = "Range" Then
        If Application.Caller.Row <> 65536 And Application.Caller.Column <> 1 Then
            LookOneBack = Application.Caller.Offset(1, -1).Text
        End If
    End If

End Function
Reply With Quote
  #5 (permalink)  
Old February 20th, 2006, 10:41 AM
Registered User
 
Join Date: Feb 2006
Location: , , .
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok thanks, I think this works and it resolves the bug. When I implement the public function it looks like this:

Public Function ValueOneBack() As Variant

    ValueOneBack = ""

    Application.Volatile

    If TypeName(Application.Caller) = "Range" Then
        If Application.Caller.Row <> 65536 And Application.Caller.Column <> 1 Then
            ValueOneBack = Application.Caller.Offset(1, -1).Value
        End If
    End If

End Function


Public Function MatchOneBack() As Integer
    Dim LookupValue As Variant
    Dim rngMatrix As Range

    Application.Volatile

    LookupValue = ValueOneBack()

    Set rngMatrix = Worksheets(1).Range("B1:B4")

    If rngMatrix.Find(What:=LookupValue, _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        MatchCase:=False) Is Nothing Then
        MatchOneBack = 0
    Else
        MatchOneBack = 2
    End If

    Set rngMatrix = Nothing

End Function

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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
Run Time Error 424 "Object Required" paul31berks Access VBA 8 October 31st, 2010 04:49 PM
VBA Object required - RTE 424 dbrother Access VBA 2 November 2nd, 2007 04:06 PM
ReportViewer runtime error:Object reference not se gobotsoup Classic ASP Databases 0 March 13th, 2007 04:37 PM
Microsoft VBScript runtime error '800a01a8' object stevesole Access ASP 9 June 7th, 2004 08:13 PM
runtime error '800a01a8' Object Required: 'Session footohi Classic ASP Basics 1 July 17th, 2003 06:32 PM



All times are GMT -4. The time now is 03:13 AM.


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