Runtime error 2

[4thorns]

Hi all. I have tried for quite a while to figure out what is wrong with this code.
I've attached an image of the code and the error. It has to do with the vlookup portion of the code.
I want a message box that shows the notes determined by the vlookup.
I think it has something to do with the way I've set up the offset. Not sure
Any advice?

Thanks,
Doug

 

[IRstuff]

Don't you think that the actual code might be more useful than a picture?

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!

 

[IDS]

I agree with IRstuff.

Could passing the Target cell ByVal be the problem?
Which line does the error message occur on?

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[4thorns]

Sorry Guys. Here is the code.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim isect As Range
Set isect = Intersect(Target, Range("D"))
Dim a1 As Range
Dim targetoffset As String
Dim notes As String
Set a1 = Range("a1:a1")
targetoffset = Target.Offset(0, -2).Value
notes = Application.VLookup(targetoffset, Sheet1.Range("B5:Q11"), 16, False)
If Not isect Is Nothing Then
a1 = targetoffset

MsgBox "Notes: " & notes
End If
Cancel = True

 

[IRstuff]

Seriously? Where's the context, i.e., the rest of the sheet? It's a subroutine, so it's kind of meaningless without knowing how you are actually calling it.

TTFN
faq731-376

Need help writing a question or understanding a reply? forum1529

Of course I can. I can do anything. I can do absolutely anything. I'm an expert!

 

[4thorns]

IRstuff, the code is called by d double click in column "D".

Doug, the error code is related to the notes=......vlookup.

Thanks,
Doug

 

[yakpol]

4thorns,

Two suggestions:
1. Declare targetoffset as Variant
2. try application.worksheetfunction.vlookup instead of application.vlookup

Regards,

yakov

 

[4thorns]

Yakov. It looks like your suggestions made the difference. It seems to be working fine now.
I'm sure I will have to make changes to it as the spreadsheet progresses but it's a good start.
It may not be the prettiest code but with the limited time I have to work on it it'll suffice for now.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim isect As Range
Set isect = Intersect(Target, Range("D"))
Dim G2 As Range
Dim targetoffset As Variant
Dim notes As String
Set G2 = Range("G2:G2")
targetoffset = Target.Offset(0, -2)
On Error Resume Next
notes = Application.WorksheetFunction.VLookup(targetoffset, Sheet2.Range("B5:Q11"), 16, False)
If Not isect Is Nothing Then
G2 = notes
MsgBox "Notes: " & notes

End If

Cancel = True

End Sub

Thanks again everyone for your replies.
Doug

 

[yakpol]

Glad it helped!