VBA table column loop, validate populated cells in row

[crossej]

I am using Excel 2007, attempting this in VBA.

I am creating a spreadsheet, which will record user actions, and the associated timestamp. I will have several actions, and just need to record the last timestamp.

I need to loop through a table's column, and determine if I should populate a date field, or remove it.
In the example below, I want to loop through the Completed Date column. For each of the cells, look to see if the two columns to the left ( Permissions? and Completed? cells ) are populated by a signature or if they are blank.
In this example, rows 1, 2, 3, 6 should end up with a date in the Completed Date column. Rows 4, 5 should end up with nothing in the Completed Date column.


This is a portion of my table. I will have other columns which will act similar.
I also do not know the number of rows which will appear in the table.
Because of that, I need a generic function.

I'd prefer to have a generic function, where I pass in the column to check ( Completed Date ) and possibly range size ( 2 ) of cells to validate.
The function will loop through all available table column cells. It will then look for empty values in the cell range on the same row.

<table border="1"><tbody><tr><td>Row</td><td>Permissions?</td><td>Completed?</td><td>Completed Date</td></tr><tr><td>1</td><td>signature</td><td>signature</td><td>02/28/2011</td></tr><tr><td>2</td><td>signature</td><td>signature</td><td>02/28/2011</td></tr><tr><td>3</td><td>signature</td><td>signature</td><td>02/28/2011</td></tr><tr><td>4</td><td>signature</td><td>
</td><td>
</td></tr><tr><td>5</td><td>
</td><td>signature</td><td>
</td></tr><tr><td>6</td><td>signature</td><td>signature</td><td>02/28/2011</td></tr></tbody></table>

I've been investigating this for about a week, and finally thrown up my hands and will be starting fresh. All attempts at programming this has failed.
I am stuck in:
How to loop over a column cell, then determine what row I'm in
How to check a range in the current row to see if all rows are signed off
How to update the column to the current date based on the results of the range test

All help is appreciated. I am new to this and now, frustrated.

 

[crossej]

Yeah, I think that's where we're off. The 2007 tables are much better than the 2003 counterparts. And keeps me from hardcoding things I cannot for practical reasons.

But I'm getting stuck in trying to get it to function. I'm close, but I can't get the syntax right apparently.

Thanks for your help though. It did help my understanding!

 

[crossej]

Hey - I GOT IT!!!

My procedure was modifying a cell. So it was then calling itself! That was the source of my latest issue.
Once I combined your help with the 2007 structure, and disabled events, I got it working. phew!

Private Sub Set_Date(DateTest As Range, Test_Header As Range, Test_Totals As Range)

    Application.EnableEvents = False
    For Each c In DateTest.Cells
        If Intersect(c, Test_Header) Is Nothing And Intersect(c, Test_Totals) Is Nothing Then
            If Len(c.Offset(, -1)) <> 0 And Len(c.Offset(, -2)) <> 0 Then
                c.Value = Now
            Else
                c.Value = ""
            End If
        End If
    Next c
    Application.EnableEvents = True

End Sub

Thanks again.

- Eric