Runtime error
Runtime error
(OP)
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
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





RE: Runtime error
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
RE: Runtime error
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/
RE: Runtime error
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim isect As Range
Set isect = Intersect(Target, Range("D: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
RE: Runtime error
TTFN

FAQ731-376: Eng-Tips.com Forum Policies
Need help writing a question or understanding a reply? forum1529: Translation Assistance for Engineers
Of course I can. I can do anything. I can do absolutely anything. I'm an expert!
RE: Runtime error
Doug, the error code is related to the notes=......vlookup.
Thanks,
Doug
RE: Runtime error
Two suggestions:
1. Declare targetoffset as Variant
2. try application.worksheetfunction.vlookup instead of application.vlookup
Regards,
yakov
RE: Runtime error
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: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
RE: Runtime error