VBA table column loop, validate populated cells in row

crossej

New Member
Joined
Feb 28, 2011
Messages
7
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. :-)
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Hi,

How about this macro which assumes:
* Data is in Sheet1 starting at row 2
* Signatures are in columns A & B
* completed Date to be placed in column C
Code:
Option Explicit

Sub GetCompletedDate()
Dim lRow As Long
Dim vaCur() As Variant
Dim WS As Worksheet


Set WS = Sheets("Sheet1")
For lRow = 2 To WS.Cells(Rows.Count, "A").End(xlUp).Row
    If Len(CStr(WS.Cells(lRow, 1).Value)) <> 0 _
    And Len(CStr(WS.Cells(lRow, 2).Value)) <> 0 Then WS.Cells(lRow, 3).Value = Format(Date, "mm/dd/yyyy")
Next lRow

End Sub
 
Upvote 0
Not quite as generic as I'll need it. Although that helped me with something else.

There will be plenty of other things on the sheets, so I'll need to be using table references instead of referencing the stylesheet itself.

I can pass this into the function which will provide the table, and range of cells. In this case, "5" represents the Completed Date column.
Call Set_Date(ListObjects("Table_Mailboxes").ListColumns(5).Range )

I can loop over that range in the Sub via:

Private Sub Set_Date(DateTest As Range)
For Each c In DateTest.Cells
Next c
End Sub

But I'm stuck there. In the above loop, I want to look at the two columns to the left of the cell. Of course, I may be way off in how I should be doing it.
 
Upvote 0
Hi,

You can reference these cells by something like c.offset(,-2).Value and c.Offset(,-1).Value

You can, of course, write a UDF similar to this:
Code:
Option Explicit

Function GetCompletedDate(ByVal Sig1 As String, ByVal Sig2 As String) As Variant
If Len(Sig1) <> 0 And Len(Sig2) <> 0 Then
    GetCompletedDate = Date
Else
    GetCompletedDate = ""
End If
End Function
or achieve exactly the same effect with a formula, but in both cases, it uses the current date which is, of course, liable to change, e.g.:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: right;;"></td><td style="font-weight: bold;text-align: center;;">UDF</td><td style="font-weight: bold;text-align: center;;">Formula</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="font-weight: bold;;">Permissions?</td><td style="font-weight: bold;;">Completed?</td><td style="font-weight: bold;text-align: center;;">Completed Date</td><td style="font-weight: bold;text-align: center;;">Completed Date</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Sig 1</td><td style=";">Sig 101</td><td style="text-align: center;;">28-Feb-11</td><td style="text-align: center;;">28-Feb-11</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Sig 2</td><td style=";">Sig 102</td><td style="text-align: center;;">28-Feb-11</td><td style="text-align: center;;">28-Feb-11</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Sig 3</td><td style=";">Sig 103</td><td style="text-align: center;;">28-Feb-11</td><td style="text-align: center;;">28-Feb-11</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style=";">Sig 104</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Sig 5</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Sig 6</td><td style=";">Sig 106</td><td style="text-align: center;;">28-Feb-11</td><td style="text-align: center;;">28-Feb-11</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C3</th><td style="text-align:left">=GetCompletedDate(<font color="Blue">A3,B3</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D3</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">ISBLANK(<font color="Green">A3</font>),ISBLANK(<font color="Green">B3</font>)</font>),"",TODAY(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C4</th><td style="text-align:left">=GetCompletedDate(<font color="Blue">A4,B4</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D4</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">ISBLANK(<font color="Green">A4</font>),ISBLANK(<font color="Green">B4</font>)</font>),"",TODAY(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C5</th><td style="text-align:left">=GetCompletedDate(<font color="Blue">A5,B5</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D5</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">ISBLANK(<font color="Green">A5</font>),ISBLANK(<font color="Green">B5</font>)</font>),"",TODAY(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C6</th><td style="text-align:left">=GetCompletedDate(<font color="Blue">A6,B6</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D6</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">ISBLANK(<font color="Green">A6</font>),ISBLANK(<font color="Green">B6</font>)</font>),"",TODAY(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C7</th><td style="text-align:left">=GetCompletedDate(<font color="Blue">A7,B7</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D7</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">ISBLANK(<font color="Green">A7</font>),ISBLANK(<font color="Green">B7</font>)</font>),"",TODAY(<font color="Red"></font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C8</th><td style="text-align:left">=GetCompletedDate(<font color="Blue">A8,B8</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">D8</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">ISBLANK(<font color="Green">A8</font>),ISBLANK(<font color="Green">B8</font>)</font>),"",TODAY(<font color="Red"></font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
I'm much closer than I was yesterday. Thanks for the help!

I'm receiving an error during the loop at present.

This is my current function:

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

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

End Sub


When I make a change to the spreadsheet, the first loop runs, and successfully updates the cell value to Now, or to "" if the tests fail.

However, after setting the value, the function then errors on that step.
I receive: Method 'Value' of object 'Range' failed

The function stops at the first loop, without continuing. What I'm finding online for this error doesn't make sense based on the result I'm seeing. Any thoughts on this?

Thanks again
 
Upvote 0
Hi,

what are your parameters to the function?

Maybe a sheet change even is what you're looking for, e.g.:
Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lRow As Long
Dim rCur As Range

If Intersect(Target, Columns("A:B")) Is Nothing Then Exit Sub

Application.EnableEvents = False
If Target.Row > 1 Then
    For Each rCur In Target.Resize(, 1)
        lRow = rCur.Row
        If Len(CStr(Range("A" & lRow).Value)) <> 0 _
        And Len(CStr(Range("B" & lRow).Value)) <> 0 Then
            Range("C" & lRow).Value = Now
        Else
            Range("C" & lRow).Value = ""
        End If
    Next rCur
End If
Application.EnableEvents = True
End Sub
 
Upvote 0
This is how I'm invoking the function:

' DEV Date
Call Set_Date(ListObjects("Table_Mailboxes").ListColumns(5).Range, _
ListObjects("Table_Mailboxes").HeaderRowRange, _
ListObjects("Table_Mailboxes").TotalsRowRange)


I'll take a look at the differences in what you wrote later today. Just wanted to reply to your question first.
 
Upvote 0

Forum statistics

Threads
1,224,590
Messages
6,179,762
Members
452,940
Latest member
rootytrip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top