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

    Conditional Selection (Excel 2000 >)

    Hi all,

    I would like to get this thread going that will collect various ways (for any contributors) to customize Excel to enable conditional selection. Selection is a vital part of excel, and I am aware of the options under GO TO | SPECIAL... to select blanks and formulas and comments etc!!

    How about some code that can select cells with various types of formatting or fonts... or code to select various values in cells. How abot selection techniques to select alternating rows or columns etc.

    I know there are freeware downloads for add-ins etc..But the real reason for this post is more for a learning curve to find out HOW to go about building code to perform varoius selections. Think of it as a thread for useful selection code that users can collect and use!

    Hans already provided me with code to select duplicates in <post#=485599>post 485599</post#>. I have already used it with success in a WB or two. Thanx again Hans.

    So, how about some more interesting pieces of code to select cells based on conditions!
    Regards,
    Rudi

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Selection (Excel 2000 >)

    How about this generic approach?

    The example selects all cells with a white interior.

    <pre>Option Explicit

    Function FindCells(ByRef oRange As Range, ByVal sProperties As String, _
    ByVal vValue As Variant) As Range
    Dim oResultRange As Range
    Dim oArea As Range
    Dim oCell As Range
    Dim bDoneOne As Boolean
    Dim oTemp As Object
    Dim vProps As Variant
    Dim lCount As Long
    Dim lProps As Long
    vProps = Split(sProperties, ".")
    lProps = UBound(vProps)
    For Each oArea In oRange.Areas
    For Each oCell In oArea.Cells
    Set oTemp = oCell
    For lCount = 0 To lProps - 1
    Set oTemp = CallByName(oTemp, vProps(lCount), VbGet)
    Next
    If CallByName(oTemp, vProps(lProps), VbGet) = vValue Then
    If bDoneOne Then
    Set oResultRange = Union(oResultRange, oCell)
    Else
    Set oResultRange = oCell
    bDoneOne = True
    End If
    End If
    Next
    Next
    If Not oResultRange Is Nothing Then
    Set FindCells = oResultRange
    End If
    End Function

    Sub test()
    FindCells(ActiveSheet.UsedRange, "Interior.ColorIndex", 2).Select
    End Sub
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Conditional Selection (Excel 2000 >)

    There are situations in which you explicitly want to select cells in code, for example in order to inspect them visually. There are also situations in which it is necessary for further processing, but in general, you do not need to select cells if you want to do something with them. The following code

    Worksheets("Sheet2").Activate
    Range("B4").Select
    Selection.Interior.ColorIndex = 6

    can be replaced by the single line

    Worksheets("Sheet2").Range("B4").Interior.ColorInd ex = 6

    This is not only shorter, but also more efficient - switching between worksheets and selecting ranges constitutes considerable overhead.

    Another example: the equivalent of Edit | GoTo | Special... in code is the SpecialCells method of the Range object. You can select all cells with formulas in the active worksheet with

    ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas) .Select

    But if you want to do something with them, it is more efficient to set a range without selecting it:

    Dim rng As Range
    Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas)

    Jan Karel's example in fact shows the same approach: his generic routine returns a range object. One of the things you can do with it is to select the range, but it can also be processed in other ways.

    Another example is <post#=273200>post 273200</post#> by Rory for working with conditional formatting

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

    Re: Conditional Selection (Excel 2000 >)

    Thanks Jan and Hans for the samples!

    The reason for asking for the code is to learn more about how to work with the range object, and manipulate it as needed. Selecting cells is an NB part of running actions on the spreadsheet. I am aware that you do not need to select cells in code to run actions on it, but rather to assingn the calculated range o a range variable. Ultimately its the same thing though! I need to learn how to set up code that will either select the cells in Excel if I want to run excel menu commands on the cells, or get a reference to the cells that meet the conditions to run other code instructions on the range.

    Both your examples are valuable to me, if it selects a range or assigns it to a variable. Either case I can use it!

    Tx
    Regards,
    Rudi

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

    Re: Conditional Selection (Excel 2000 >)

    Hans, here is a weird Q.

    Is it possible to convert the string value from an inputbox into a line of code. For example:
    myCode = inputbox("Supply the action to perform on the active cell!")
    The user types into the inputbox: "Interior.colorindex = 3"

    Back in code it is interpreted like:
    Sub FormatData()
    myCode = inputbox("Supply the action to perform on the active cell!")
    Activecell.myCode
    End Sub

    Obviously this will not work now...but can it be modified to make this work????
    Tx
    Regards,
    Rudi

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Conditional Selection (Excel 2000 >)

    Hi Rudi,

    If you look a bit closer at my code, you'll see that this is in fact possible. My code does exactly what you describe, except it reads the value of the property passed to the function in stead of writing it. If you study the CallByName function, you'll see the last argument I used (vbGet) can be replaced with vbLet or vbSet (the first for a property, the second for an object) so you are in fact assigning a value/object to that property/object.

    The only problem with CallByName is that the string with the property must contain a single entity, so a construction like Interior.ColorIndex needs to be split into Interior and colorindex separately (which is what the split() and looping construction in my function is achieving).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: Conditional Selection (Excel 2000 >)

    It is possible to use code to write code by setting a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library. See for example Programming To The VBE for an explanation and examples.

    However, I don't think what you want is a good idea. The chances for messing things up are far too large. How are you going to check that the code supplied by the user is valid? And that it doesn't do undesirable things? Writing code in code is difficult enough to get right if you do it all yourself, it could be a disaster if you let the user do it.

    Added later: see Jan Karel's much better reply below. (I still wouldn't like to give end users this capability).

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

    Re: Conditional Selection (Excel 2000 >)

    Jan. Thanx for the input. I will be studying up the code tonight! (Not had chance yet since you posted it). I appreciate the code and am looking forward to testing it out.
    Hans, thanx for all the input too. I will certainly be working on these and see what I can develop from the sample code.

    As I mentioned at the beginning of the thread...this is all for the sake of learning and developing my skills in VBA.

    BIG <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    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
  •