Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Intersect questions (All)

    Hi all,

    My first question:
    Is Application.Intersect() and 'just' Intersect() the same thing? In many cases I simply see code posted in VBA like this: If Not Intersect(...) Is Nothing Then... , yet the only example in the VBA Help files points to an Intersect Method available from the Application Class.

    Secondly:
    Is there a way to select only the non blank cell within an intersected area? Say I have code like this...

    Option Explicit
    Sub TestIntersect()
    Dim myData As Range
    Set myData = Application.Intersect(Range("A1:E10"), Range("C4:H20"))
    If Not myData Is Nothing Then
    myData.Select
    Else
    MsgBox "No data to select"
    End If
    End Sub

    Thirdly:
    Is there a way to create a "Target" range like the ones you see in the Worksheet Event Macros. (ByVal Target As Range)??
    This seems to be a very dynamic range reference which is often seen in use in the Intersect Function (esp. in an event macro). I would like to know if a Target Range Reference can be created in a Standard Module?
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intersect questions (All)

    Rudi

    You can add additional cells within a range ie
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:A10,B2,C510")) Is Nothing Then
    MsgBox " You Changed Stuff"
    End If
    End Sub
    </pre>


    This basically means you can change anything on the worksheet but if you change contents in A1:A10, B2 and C5:C10 you will get a message.
    Jerry

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intersect questions (All)

    I noticed that the arguments list goes all the way up to 30.

    Tx Jezza, but your reply is telling me what cells were changed. I just want the non blank cell within the intersected area to be selected.
    Regards,
    Rudi

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intersect questions (All)

    I seems like the answer to number two is to use a loop procedure within the intersected range. I am actually trying to make intersect do something that it is not programmed to do. I think the best approach is to declare the intersected range and test with a for each cell loop where the cell with the non blank value is, then select it. Tell me if there is another way as I am never keen on using loops if it is not required. It is not very optimal.
    TX
    Regards,
    Rudi

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Intersect questions (All)

    1) They are the same. In many cases, you can omit the Application prefix without problems. There are exceptions. for example Application.InputBox is different from "plain" InputBox. The latter is equivalent to VBA.InputBox. The Excel version has more bells and whistles.

    2) "The" non blank cell? How do you know there is only one?

    3) No, Target has no meaning outside event procedures. Of course, you can specify a range argument for your own procedures, but it will not be pre-filled by Excel automatically the way Target is.

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Intersect questions (All)

    Once you have the intersection range, you can issue a Find on it, if there's only one non-blank cell. If there's more you can loop using FindNext.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intersect questions (All)

    Tx for those answers.

    About #2,
    For now I know there is only one non blank cell, as it is my own experiment. I did not want to 'complicate' the scenario so I thought I should work with just one. It could always (depending on the solution) be adjusted to more non blanks. Is my looping logic correct or is there a better way to find that cell in an intersected range.
    TX
    Regards,
    Rudi

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Intersect questions (All)

    This is general and will select the non-blank cells in your intersect:

    <pre>Option Explicit
    Sub SelectNonBlank()
    Dim myData As Range
    Dim rConst As Range
    Dim rForms As Range
    Dim rNonblank As Range
    Set rConst = Nothing
    Set rForms = Nothing
    Set myData = Application.Intersect(Range("A1:E10"), Range("C4:H20"))
    On Error Resume Next
    Set rConst = myData.SpecialCells(xlCellTypeConstants)
    Set rForms = myData.SpecialCells(xlCellTypeFormulas)
    On Error GoTo 0
    If rConst Is Nothing Then
    If rForms Is Nothing Then
    MsgBox "There are no Non-Blank Cells"
    Else
    Set rNonblank = rForms
    End If
    Else
    If rForms Is Nothing Then
    Set rNonblank = rConst
    Else
    Set rNonblank = Union(rConst, rForms)
    End If
    End If
    rNonblank.Select
    Set myData = Nothing
    Set rNonblank = Nothing
    Set rConst = Nothing
    Set rForms = Nothing
    End Sub</pre>


    Steve

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Intersect questions (All)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>Rudi wanted the non-blank cells selected. Your code will select the blank cells

    Steve

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect questions (All)

    OOPS! I misread the post. Thanks for pointing it out.
    Legare Coleman

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Intersect questions (All)

    does this do what you want?

    <code>
    Intersect(Range("A1:E10"), Range("C4:H20")).SpecialCells(xlCellTypeBlanks).Se lect
    </code>


    Sorry, I misread your original post. The code above selects all of the blank cells, not the non-blank cells. Thanks to Steve for finding my error.
    Legare Coleman

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intersect questions (All)

    Steve,

    How did you paste your code? Did you use "pre" tags? When I try copy your code and paste it into Word to get it in block format, it pastes similar to NotePad. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> I have not had this problem before??
    Attached Images Attached Images
    Regards,
    Rudi

  13. #13
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intersect questions (All)

    Hi Steve,

    TX. The code is great. It selects all the data in the intersect. I notice the Union function too. this has also been a very interesting function I picked up from Hans long time ago. It's a first time in a long time that I have come across this one again. Cheers

    TX Jezza, Hans and Legare for the input. Appreciate you guys!!!

    PS: If there are no non blank cells, the code debugs after the msgbox that informs no non blank cells are available. It says Err:91 - Object variable or with block not set. No hassles about it. It is experimental code at this stage.
    Cheers
    Regards,
    Rudi

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Intersect questions (All)

    He did use <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags, but if I copy/paste into Word, I get separate lines.
    Does the problem persist if you quit and restart your browser? If you quit and restart Word?

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Intersect questions (All)

    It seems to be resolved now - suddenly??!!

    I pasted it three times on the same blank Word doc, deleted and repasted. And it kept pasting like the attached. Then I went to WordPad and pasted it there with no better results. Out of frustration I pasted it back in Word but on a new line underneath the first paste (after I sent the attachment post), and it pasted great!

    Subsequently I have copied Steves code again and pasted to test and it works fine again now. I dunno what happened the first time around??? Mystery!
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •