Runtime Error 424: Object needed

tobian · February 18th, 2006, 06:00 AM

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

tobian · February 18th, 2006, 10:18 AM

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

tobian · February 18th, 2006, 10:48 AM

Somewhere I use a wrong data type, but where?

maccas · February 20th, 2006, 04:50 AM

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

tobian · February 20th, 2006, 10:41 AM

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