Loop through a table range and evaluate specific columns 1

[richand1]

Hi all,

I've found many things like what I want, but nothing that gives me it exactly. I've been trying to get this right for 2 days now....

I have an Excel table, "ActionPlan", which starts at cell B101. The table can be variable length and forms a template which many colleagues will take and fill in with information.

I'd like to loop through every row of the table, evaluate values in specific columns, and perform an action until the last used row of the table. The code I have - which is an absolute mess, so apologies, but hopefully gives an idea as to what I'm attempting to accomplish - is this:

Sub Testing_Routines_2()

Dim ws As Worksheet
Dim wb As ThisWorkbook
Dim tbl As ListObject
Dim tblrow As Range
Dim LastRow As Range

Set wb = ThisWorkbook
Set ws = ThisWorkbook.ActiveSheet
Set tbl = ThisWorkbook.ActiveSheet.ListObjects("ActionPlan")
tblrow = ThisWorkbook.ActiveSheet.ListObjects("ActionPlan").DataBodyRange
LastRow = tbl.tblrow.Cells(Rows.Count, 1).End(xlUp).Row

For i = 1 To LastRow
    If wb.ws.tbl.tblrow("AV" & i).Value <> 0 And _
        ActiveSheet.Range("DL & i").Value = " " Then
        ActiveSheet.Range("DL & i").Value = Now()
        End If
  Next i

End Sub

I have a few different loops to complete where the columns and values to check are different. Obviously in this example I'd like to enter today's date into a cell on the active row if the conditions are met.

As an added feature, I'd like the code to run on save - which isn't a problem for me to code - and during the procedure I will remove any filters on the table because I've read that tables can be fiddly when filters are on.

Thanks a lot.

Rich

Edit: spelling

 

[SkipVought]

Hi,

Seems that you have converted your table to a Structured Table named ActionPlan. That's a good move!

Sub Testing_Routines_2()
'
    Dim r As Range, rng As Range
        
    For Each r In [ActionPlan[#DATA]]
        Select Case Intersect(r.EntireRow, Range("AV1").EntireColumn).Value
            Case Is <> 0
                Set rng = Intersect(r.EntireRow, Range("DL1").EntireColumn)
                If rng.Value = "" Then
                    rng.Value = Now
                End If
        End Select
    Next

End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[richand1]

Skip,

As always, thank you very much for your excellent response - this works flawlessly.

Thank you,
Rich

 

[SkipVought]

Personally, I would not use column references (AV & DL) rather I’d use Table Headings.

Suppose that the AV heading were Act Typ...
[tt]
Intersect(r.EntireRow, [ActionPlan[Act Typ]])
[/tt]
Using Headings give better self-documenting code.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[richand1]

Thank you, Skip. I see what you're saying and it makes absolutely sense.