If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

**wideboy** · 05-20-2016, 06:56 AM

Hi

I forgive my ignorance I am new to VBA and have ben trying to cut and paste various bits of code together to achieve my goal

end goal =

double click a cell in a column from which an embedded file (outlook message) is opened

The column contains 100's of entries but these boil down to 5 variants (each variant has its own email)

I can get the code to open up a single object but not to open up the object related to the value in the column

any help would be much appreciated
Thanks

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim Object99 As String

Object99 = Application.WorksheetFunction.VLookup(Objective, Worksheets("email").Range("b:c"), 2, False)
'MsgBox Object1

If Not Intersect(Target, Range("AD:AD")) Is Nothing Then

Application.ScreenUpdating = False

Sheets("email").OLEObjects("Object99").Verb

Me.Activate

Application.ScreenUpdating = True

Cancel = True

End If

End Sub

**:) Sixthsense :)** · 05-20-2016, 07:12 AM

Dim Object99 As String

Not sure why you quoted Object99 in double quotes which will be considered as Text entry instead of variable value.

Please Login or Register  to view this content.

**wideboy** · 05-20-2016, 07:15 AM

Hi Apologies this was an error on my part

I have removed the "" but it still doen snot work??

**:) Sixthsense :)** · 05-20-2016, 07:18 AM

Object99 = Application.WorksheetFunction.VLookup(Objective, Worksheets("email").Range("b:c"), 2, False)

Objective refers to what?

**wideboy** · 05-20-2016, 07:21 AM

im sorry again, it was part of some code I copied and pasted...it does not mean anything

**:) Sixthsense :)** · 05-20-2016, 07:23 AM

Originally Posted by wideboy

im sorry again, it was part of some code I copied and pasted...it does not mean anything

Okay, try this...

Please Login or Register  to view this content.

**wideboy** · 05-20-2016, 07:26 AM

:D

I feel rather inept...thank you sir!

so would the IF statement need to preceed the DIM statements?

Thanks mate!

**:) Sixthsense :)** · 05-20-2016, 07:35 AM

Originally Posted by wideboy

IF statement need to preceed the DIM statements?

No...

Using Dim Object99 As String will tell excel to allocate 10 bytes of memory for Object99 variable.

So excel will allocate 10 bytes for Object99 whenever double click event occurs on any cell. It is waste of memory and unnecessarily this code will run on all double clicks.

But your actual intention is to allocate memory for Object99 variable when the double click occurs in AD column only. So using the variable declaration inside the If() will avoid the unnecessary memory allocation and unncessary vlookup process.

Hope it's clear now.

PS: Also note that I changed Objective to Target.Value. Target refers to double clicked cell (Event Range/Cell)

**wideboy** · 05-20-2016, 07:41 AM

Thanks, why the 2 lines of screen updating? one true and one false?

Screen updating refers to the onscreen following of the macro right?

**:) Sixthsense :)** · 05-20-2016, 07:46 AM

Originally Posted by wideboy

why the 2 lines of screen updating? one true and one false?

When we don't want the users to get notified about the changes/screen movements then before doing those actions we have to turn off the screen updating. When we finished all work then we have to turn it ON. Because screen updating is an application setting which affects all macro codes if it is turned off.

Also turning off screen updating will increase the speed of the macro since excel can silently do all it's stuffs without showing all the actions to the users.

**wideboy** · 05-20-2016, 07:47 AM

Thanks mate, you are a legend...have a great weekend!

**:) Sixthsense :)** · 05-20-2016, 07:48 AM

You're welcome and have a nice weekend to you too...

Also, thanks for the feedback.

If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

LinkBack

Thread Tools

Rate This Thread

Display

If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Re: If Not Intersect(Target, Range("AD:AD")) Is Nothing Then,,,,VLOOKUP??

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] If Target.Cells(1).Address = Range("$B$5": ..(?)..)

[SOLVED] Can i use 2 different "Worksheet_Change(ByVal Target As Range)" on a sheet?

Don't know the Forumlas with problem either using "vlookup" "if" "and" or "index" "match"

[SOLVED] Multiple "ByVal Target As Range"

Newbie How to delete macro "Private Sub Worksheet_SelectionChange(ByVal Target As Range)<"

Getting "..object _worksheet failed..." error with intersect for cell and named range.

Need help creating VBA looping code in "Worksheet_Change(ByVal Target As Range)"

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions