×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Loop through a table range and evaluate specific columns

Loop through a table range and evaluate specific columns

Loop through a table range and evaluate specific columns

(OP)
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:

CODE

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

RE: Loop through a table range and evaluate specific columns

Hi,

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

CODE -->

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Loop through a table range and evaluate specific columns

(OP)
Skip,

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

Thank you,
Rich

RE: Loop through a table range and evaluate specific columns

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

Suppose that the AV heading were Act Typ...

Intersect(r.EntireRow, [ActionPlan[Act Typ]])

Using Headings give better self-documenting code.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Loop through a table range and evaluate specific columns

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close