Consulting

Results 1 to 5 of 5

Thread: Finding a value in my table

  1. #1
    VBAX Regular
    Joined
    Jul 2019
    Posts
    51
    Location

    Finding a value in my table

    I am trying to find a cell in this table based on certain criteria. This is the criteria:
    1) Search "Start Date" column top to bottom for the cells with a colorindex of 35; searching only the rows which are not hidden by filters
    2) Find the first cell in column "Start Date" with a colorindex of 35 for which the adjacent cell in column "End Date" has an indexcolor other than 35
    3) Get the date in that particular cell found
    4) Filter out all days prior to that day; show only that cell and the ones below it
    5) Keep the column sort in place; do not clear the sorting
    6) Then I will assign it to a button

    I haven't done much with ListObjects (tbls, pvtTbls, etc.) and I come from a different programming program. Here's my SIMPLE code so far and I keep hitting road blocks so I'm not sure where I'm headed...

    Private Sub CustomTest()
        'Intiansiate objects and setup variables
        Dim c As Range, tbl As ListObject, FoundCell As Range
        
        'Set object/variable values
        Set tbl = ActiveWorkbook.Worksheets(1).ListObjects("Table1")
        
        For i = 1 To tbl.Range.Rows.Count
            MsgBox Range(i, tbl.Range.Columns(2))
            
        Next i
        
        MsgBox tbl.Range.Rows.Count
    End Sub

    1.JPG

  2. #2
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello mongoose,

    I am assuming all the dates are in the same year, correct?

    What version of Office are you using?
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  3. #3
    VBAX Regular
    Joined
    Jul 2019
    Posts
    51
    Location
    2013. Yes they are but I would like to plan for if they are not as well.

  4. #4
    VBAX Expert Leith Ross's Avatar
    Joined
    Oct 2012
    Location
    San Francisco, California
    Posts
    552
    Location
    Hello Mongoose,

    If I understand what you want to do then this code should do it.

    Private Sub CustomTest()
    
    
        'Intiansiate objects and setup variables
        Dim tbl As ListObject
        
            'Set object/variable values
            Set tbl = ActiveWorkbook.Worksheets(1).ListObjects("Table1")
        
            With tbl.AutoFilter
                ' Start Date
                With .Filters(2)
                    .Operator = xlFilterCellColor
                    .Criteria1.ColorIndex = 35
                End With
            
                .ApplyFilter
                
                ' End Date
                With .Filters(3)
                    .Operator = xlFilterCellColor
                    .Criteria1.ColorIndex = xlColorIndexNone
                End With
                
                .ApplyFilter
            End With
        
    End Sub
    Sincerely,
    Leith Ross

    "1N73LL1G3NC3 15 7H3 4B1L17Y 70 4D4P7 70 CH4NG3 - 573PH3N H4WK1NG"

  5. #5
    VBAX Regular
    Joined
    Jul 2019
    Posts
    51
    Location
    I'm sorry for the delay in response. I've been out of town. I've tried the code you posted but it hides all rows in the table.

    The first image shows the data before I run the macro. The 2nd shows the data after the macro has run.

    2.JPG 3.JPG

    Here are the conditions I am trying to accomplish..
    4.JPG

Tags for this Thread

Posting Permissions

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