Using variables in table formulas with VBA

[bkneller]

Hi,

I'm creating an Excel 2010 workbook for processing a bunch of scientific data dumped from an instrument. So far I've been able to code for creating a table from the dumped data. Now I would like to be able to add a formula to a column to calculate an area percentage--it just divides the value from the current row of the "Area" column by the total of the "Area" column. If I hard-code in the name of the table (in this case Tbl_071-0272.D), it works fine.

However, I would like to be able to store the table name in a variable so I can cycle through many data sets. When I try this, I get error 1004, though.

So this works:

DataTbl.ListColumns(7).DataBodyRange.Cells(1).Formula = _
                "=[@Area]/Tbl_071-0272.D[[#Totals],[Area]]"

And this doesn't (note that DataTbl.Name = "Tbl_071-0272.D"):

DataTbl.ListColumns(7).DataBodyRange.Cells(1).Formula = _
                "=[@Area]/" & DataTbl.Name & "[[#Totals],[Area]]"

Am I doing something wrong or am I up against an Excel 2010 limitation?

Many thanks,

Byron

 

[xenou]

For reasons obscure to me, it sometimes works best to build the string first (I do this much out of habit):

Dim sFormula As String
sFormula = "=[@Area]/" & DataTbl.Name & "[[#Totals],[Area]]"
DataTbl.ListColumns(7).DataBodyRange.Cells(1).Formula = sFormula

Maybe that will get through for you.
ξ

 

[bkneller]

Xenou,

Thanks for your speedy reply. I tried that (should have mentioned it) and it didn't work.

Of course, 10 minutes after posting I had another idea that actually did work--I just took the reference to the table name out of the code entirely, reasoning that as I was only referencing the table itself (rather than another table), that it should work OK.

So I used:

DataTbl.ListColumns(7).DataBodyRange.Cells(1).Formula = _
                "=[@Area]/[[#Totals],[Area]]"

which shows up in my actual table as: "=[@Area]/Tbl_071_0272.D[[#Totals],[Area]]". Which is what I wanted.

Still getting used to tables, having only recently switched from Excel 2003 to 2010. The problem is that now it seems like the solution to every problem somehow involves a table...

Thanks again,

Byron

 

[xenou]

Good ... I'm still working out Tables myself. I think I told myself to get started on that sometime last year ... Just never seem to get around to it.