• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1207
  • Last Modified:

Structured Referencing a table from VBA

I am attempting to reference the value of a cell in a table from VBA. I would like to use structured referencing so I do not have to worry where the column is in the table. In the article at http://www.jkp-ads.com/Articles/Excel2007Tables.asp about Excel Tables the author shows some examples that work in formulas. The one that I specifically would like to reference is =Table1[[#This Row][Discount]].

So in my VBA code I have the row available to me in a variable = I. I am struggling on how to write the line of code to get the value of another column in that row.

For Example

debug.print Table1[[#This Row][Discount].value

Open in new window


I cannot seem to find any examples for working with structured referencing of a table from VBA. Any sites that discuss this would be appreciated. I know that with a good bit of code I can get the same result by converting the structured reference into a row / column format, however, I would like to learn how to take advantage of the structured referencing to make the code cleaner and more efficient.

Thanks
0
ckelsoe
Asked:
ckelsoe
  • 4
  • 2
1 Solution
 
ckelsoeAuthor Commented:
I was able to get the following to work.

debug.print Evaluate("Table1[[#This Row][Discount]]")

Open in new window


0
 
Patrick MatthewsCommented:
ckelsoe,

Please go ahead and select your own comment as the answer.  Good job :)

Patrick
0
 
ckelsoeAuthor Commented:
Well - I had to modify the code a bit to make the code above work correctly. I was just doing offsets to loop through each row. In order for the statement to evaluate properly I needed to be in the cell that was in the row I wanted to get data from. This slows the code down quite a bit but it does meet the need for now.
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ckelsoeAuthor Commented:
Figured out how to achieve the objective of the code. Open to all other suggestions to optimize.
0
 
Rory ArchibaldCommented:
It would be more efficient to return the entire column, and then use the index with the resulting array. That way you don't need to select anything. FWIW.
0
 
ckelsoeAuthor Commented:
Could you post an example of this?

My code looks something like this now:

sheets("Errors").range("A") & intErrorCounter) = Evaluate("Table1[[#This Row],[AccountNumber]]")
sheets("Errors").range("B") & intErrorCounter) = Evaluate("Table1[[#This Row],[Name]]")

Open in new window

I found that I had to be in the cell in the row that contained the data for this to work. So I move the active cell to that location then process the test of which the code above is run when there is a failure for that particular cell.
0
 
Rory ArchibaldCommented:
Assuming 'I' as an index variable:

sheets("Sheet name").range("Table1[AccountNumber]").cells(I).address

Open in new window


for example.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now