Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 18th, 2006, 06:00 AM
Registered User
 
Join Date: Feb 2006
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


 
Old February 18th, 2006, 10:18 AM
Registered User
 
Join Date: Feb 2006
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


 
Old February 18th, 2006, 10:48 AM
Registered User
 
Join Date: Feb 2006
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Somewhere I use a wrong data type, but where?

 
Old February 20th, 2006, 04:50 AM
Friend of Wrox
 
Join Date: Jun 2003
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
 
Old February 20th, 2006, 10:41 AM
Registered User
 
Join Date: Feb 2006
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






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





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