for/next referencing rows filtered on table

[beckyinDC]

Hi all!

I was trying to make use of table/list functionality (filtering and referencing) while looping. I was thinking it might allow for something more efficient /elegant than what we have which loops through every row with if/then statements.

I am starting to think I am making it more confusing than it has to be...am I missing a key that would simplify things?


I have a table with 18 columns established. For rows where the Title (col 8) is blank, I want to call an external source to get 3 values and then insert them in the appropriate columns for that record.



' Filter TPR list for where title (col 8) is blank - macro recording gave me this
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8, Criteria1:="="

'I used Intersect and specialcells-visible to get list of TPRs (in Col B) ---is there a better way???
For Each TPRnum In Intersect(ActiveSheet.ListObjects("Table1").Range.SpecialCells(xlCellTypeVisible), Range("B29:B529"))

strTitle = GetFieldValue(TPRnum, "Title")
strStatus = GetFieldValue(TPRnum, "Status")
strSeverity = GetFieldValue(TPRnum, "Severity")

'now need to insert back to row- example- I havent tried this out...
Activesheet.Range("R" & TPRnum.row & "C8") = strTitleDate


Next TPRnum
'Undo Filter
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8


Have I missed any way to reference table columns or rows in VBA in a clearer way?

 

[beckyinDC]

I am finding "structured references" seems to be the term to search on to get more info on how to reference the table data from VBA...so doing my homework now...

in this forum someone had a link to a JKP website that was helpful.

I havent digested all the info yet, but I guess that closes this concern unless anyone had any insite to share- thanks!

 

[beckyinDC]

I have 2 problems with the code below-
1) the for next loop will pull in the header row as first row it would process...can I make that not happen by adjusting the for statement...or do I need ot trap for it with an if/then inside the for/next loop?

2) my assignment of data back to a cell in the row being processed is wrong. I am hunting for an example of what I should do, but can anyone tell me that one?

thanks a bunch-

Becky


With ActiveSheet.ListObjects("Table1")
'For all rows without a title- get Tracker data
.Range.AutoFilter Field:=8, Criteria1:="="
For Each TPRnum In .ListColumns("TPR #").Range.SpecialCells(xlCellTypeVisible)

strSubmitDate = GetFieldValue(TPRnum, "Submit Date")
strCloseDate = GetFieldValue(TPRnum, "Close Date")
strTitle = GetFieldValue(TPRnum, "Title")

'this doesnt work...why?
ActiveSheet.ListObjects("Table1").Range [[#This Row],["Build Found"]] = strTitle

Next TPRnum
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=8
End With

 

[beckyinDC]

writing to explain some things about the code:

i had attempted using a different column name to insert the title to (hence "Build Found" being in code) .

the procedure completes without error

the variable strTitle does get filled from Tracker- this never gets to the table though.