Solved

Structured Referencing a table from VBA

Posted on 2011-09-22
7
628 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
  • 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 92

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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

821 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