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.
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.