Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Structured Referencing a table from VBA

Posted on 2011-09-22
7
Medium Priority
?
985 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 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
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.

 

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

Independent Software Vendors: 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!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
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…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

581 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