Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Structured Referencing a table from VBA

Posted on 2011-09-22
7
Medium Priority
?
845 Views
Last Modified: 2012-06-21
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
Comment
Question by:ckelsoe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 

Accepted Solution

by:
ckelsoe earned 0 total points
ID: 36582621
I was able to get the following to work.

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

Open in new window


0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 36582657
ckelsoe,

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

Patrick
0
 

Author Comment

by:ckelsoe
ID: 36582675
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Closing Comment

by:ckelsoe
ID: 36708130
Figured out how to achieve the objective of the code. Open to all other suggestions to optimize.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36585811
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
 

Author Comment

by:ckelsoe
ID: 36586681
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36587320
Assuming 'I' as an index variable:

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

Open in new window


for example.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question