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

    Selecting negatives... (Excel 2000 >)

    The ability to select is the most important aspect in computing...."You have to select before you can instruct..."

    I am really struggling to understand the VBA code that allows you to select groups of cells that meet the criteria difining the selection. Hans helped me with some code a while back that selects duplicate values. (I have included that code (slightly modified) in the attachment). But now I would like to select cells in a range that are negative. I need help to understand how to assign the cell reference (meeting the criteria) to the Union function. As I understand it, its the Union function that creates the array to select at the end of the code!??

    Please help me to understand the code as I would like to practice on other selection criteria and really get this understanding on this code. It can be soooo useful to design selective code. Once you have the right cells selected, then other macros can do whatever you need to the selection.!!

    PS: I know ASAP Utilities has conditional selection. The idea of this post is not that I need the ability to select conditionally, but to learn the VBA code to be able to do it myself!

    Many TX
    Regards,
    Rudi

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

    Re: Selecting negatives... (Excel 2000 >)

    If you look at the code for SelectDuops, you'll see that it uses three (3) range variables:
    - oRange is the range selected by the user.
    - oCell is a single cell within oRange; the code loops through all cells in oRange.
    - oSelect is the range containing all cells meeting the condition
    Application.WorksheetFunction.CountIf(oRange, oCell) > 1

    In SelectNegs, you are trying to do it using only two (2) range variables: you use myR both as the range selected by the user, and then add the cells meeting the condition to it. That won't do anything: the cells already belong to myR, so you end up with the range the user selected.
    All you need to do is duplicate SelectDups, rename the copy to SelectNegs and change the condition

    If Application.WorksheetFunction.CountIf(oRange, oCell) > 1 Then

    to

    If oCell < 0 Then

    Note: if you want to do more with the cells containing a negative value, you should *NOT* select them at the end of the macro, but work with the oSelect range instead. In most situations, working with a range is more efficient than working with a selection.

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

    Re: Selecting negatives... (Excel 2000 >)

    Awesome Hans....TX

    You are indicating that your original code is the way to select any cells that match a condition that I can specify in the If statement? If the answer is YES....then this is incredibily useful code to control formatting and any other action on cells selected....WOW!!!

    PS: Tx for your warning. I understand that very clearly! That is the reason why the variable was declared in the first place! To work with the variable...not the selected cells!!! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

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

    Re: Selecting negatives... (Excel 2000 >)

    Yes, you can use the code to select cells satisfying any condition (or combination of conditions) by modifying the line
    <code>
    If Application.WorksheetFunction.CountIf(oRange, oCell) > 1 Then
    </code>
    For example if you want to select all cells whose value is between 25 and 100:
    <code>
    If oCell >= 25 And oCell <= 100 Then
    </code>
    or if you want to select all cells whose value contains the letter "z":
    <code>
    If oCell Like "*z*" Then</code>

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

    Re: Selecting negatives... (Excel 2000 >)

    TX.

    One last Q!

    If I want the inverse selection of the selected spreadsheet cells, then I can use the NOT operater? EG: Not oSelect.select (Or is this too easy to be true?)
    Regards,
    Rudi

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

    Re: Selecting negatives... (Excel 2000 >)

    No, you'd have to invert the condition in the If ... Then ... line, so that you add all cells that *DON'T* meet the condition instead of those that meet it to the range:

    If Not (...) Then

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

    Re: Selecting negatives... (Excel 2000 >)

    OK...but....

    I am thinking in lines with the ASAP utility. There is an otion in the utility that inverses the selection. The user may have all negatives selected...then when the user activates the command in ASAP, there is a prompt which asks for a range surrounding the selected cells. With this range reference, the utility inverses the selection to select all positive values. Would the IF condition be reversed or is this altogether a different code procedure?

    Tx for your patience and tuition...
    Regards,
    Rudi

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

    Re: Selecting negatives... (Excel 2000 >)

    See the attached code. If you like, you can create a standalone macro from it to invert a range or selection.

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

    Re: Selecting negatives... (Excel 2000 >)

    This is a great example...
    Thank you Hans!
    (Appreciate it!)
    Regards,
    Rudi

Posting Permissions

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