Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-26-2014, 05:55 AM
tinfanide tinfanide is offline Intersect Windows 7 64bit Intersect Office 2010 32bit
Expert
Intersect
 
Join Date: Aug 2011
Posts: 311
tinfanide is on a distinguished road
Default Intersect

A ListObject (Pivot Table):



a b c
1 2 3

ActiveCell = Cell B2 (value = "2")

The test Sub works (which returns "not intersect").
The test2 Sub does not work (which returns "intersect" but actually not).

I wonder how I can put more than 1 ranges in the arguments of the Intersect function.

Thank you.
P.S.: I have also attached the Excel file (intersect.xls).

Code:
Sub test()

If intersect(ActiveCell, Range("A:A,C:C")) Is Nothing Then
    MsgBox "not intersect"
Else
    MsgBox "intersect"
End If

End Sub

Sub test2()

Dim colA As ListColumn, colC As ListColumn
Set colA = ListObjects("Table1").ListColumns("a")
Set colC = ListObjects("Table1").ListColumns("c")

If intersect(ActiveCell, Range(colA.Range, colC.Range)) Is Nothing Then
    MsgBox "not intersect"
Else
    MsgBox "intersect"
End If

End Sub
Reply With Quote
  #2  
Old 07-27-2014, 06:14 AM
tinfanide tinfanide is offline Intersect Windows 7 64bit Intersect Office 2010 32bit
Expert
Intersect
 
Join Date: Aug 2011
Posts: 311
tinfanide is on a distinguished road
Default

Code:
Sub test()  
If intersect(ActiveCell, Range("A:A,C:C")) Is Nothing Then     
MsgBox "not intersect" 
Else     
MsgBox "intersect" 
End If  End Sub  

Sub test2()  

Dim colA As ListColumn, colC As ListColumn
'''
Set colA = ListObjects("Table1").ListColumns("a").Range 
Set colC = ListObjects("Table1").ListColumns("c").Range 
'''
Dim rng As Range
Set rng = Union(colA,colC)

If intersect(ActiveCell, rng) Is Nothing Then     
MsgBox "not intersect" 
Else     
MsgBox "intersect" 
End If  
End Sub
I've found out that with the use of "Union()" function, Column A and Column C will be treated seperately whilst otherwise, it will be treated like from Column A to Column C (i.e. including Column B).
Reply With Quote
Reply

Thread Tools
Display Modes


Other Forums: Access Forums - Senior Forums

All times are GMT -7. The time now is 03:51 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft