|
Subject:
|
Runtime Error 424: Object needed
|
|
Posted By:
|
tobian
|
Post Date:
|
2/18/2006 5:00:18 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.
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 By:
|
tobian
|
Reply Date:
|
2/18/2006 9:18:27 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
|
|
Reply By:
|
tobian
|
Reply Date:
|
2/18/2006 9:48:29 AM
|
Somewhere I use a wrong data type, but where?
|
|
Reply By:
|
maccas
|
Reply Date:
|
2/20/2006 3:50:40 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.
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 By:
|
tobian
|
Reply Date:
|
2/20/2006 9:41:44 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
|
