Solved

Structured Referencing a table from VBA

Posted on 2011-09-22
7
598 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
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 the scrolling table in Microsoft Excel using the INDEX function.

803 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