INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

RE: Runtime error

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/

RE: Runtime error

(OP)
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: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

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

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

Thanks,
Doug

RE: Runtime error

2
4thorns,

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

Regards,

yakov

RE: Runtime error

(OP)
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: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

Glad it helped!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close