mongoose
01-21-2020, 05:41 AM
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
25850
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
25850