none
Finding a value in my table RRS feed

  • Question

  • 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

    It won't let me insert an image even though I am trying to verify my account with MSDN. I was going to try to post an image of the table itself so you could see it's structure. Col1: Name, Col2: start date, Col3: end date; name of table is Table1


    Tuesday, January 21, 2020 4:22 PM

All replies

  • Hi mongoose00318,

    Could you share the Excel file (or a screenshot) via cloud storage such as OneDrive, Dropbox?
    It will help me to make an answer.

    Regards,

    Ashidacchi -- http://hokusosha.com

    Wednesday, January 22, 2020 12:21 AM