Issue with Subtotals on a Table

[dgillz]

I have a table which was created via Power Query. Works exactly like I want it except I want to total a few columns. If I do this manually by going to the first blank row and clicking on the Sigma icon, the cell contents are this:

=SUBTOTAL(109,[sls_amt])

My column name is of course sls_smt. When I filer the table, the results change as desired.

However in VBA as I attempt to create this formula with simple line of code:

ActiveCell.Formula = "=SUBTOTAL(109,[sls_amt])"

I am getting "Runtime error 1004 application-defined or object-defined error". I know the desired cell is indeed active. Can anyone think of an alternate way of doing this? Or a change in my current code?

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[combo]

You used internal table reference in formula, it works only in totals row in table. Outside table full reference is required:
[tt]ActiveCell.Formula = "=SUBTOTAL(109,StructuredTableName[sls_amt])"[/tt]

combo

 

[dgillz]

By StructuredTableName, I take it you mean the actual table name in the database? Do I need a period between the table name field name?

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[combo]

Power Query output to worksheet is only as structured table. You have refer to this table. You can see the name in table tools tab if a cell inside is selected, by default it is a query name. Exceptions: (1) in case of duplicating existing table name, suffix ("_2" etc.) is added, (2) spaces in query name are replaced by "_".

The macro recorder properly records reference if a cell outside table region (incl. the totals row) is selected and SUM of whole table column is entered.

combo

 

[Andrzejek]

Do I need a period between the table name field name?

The best way is to try it and see...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[dgillz]

I'm getting a #NAME? error now. This ia s multiple table query FWIW. I also aliased the actual table name in the SQL query.

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[combo]

This ia s multiple table query FWIW

Structured table is a native excel structure with its own referencing rules and. It has headers, data range, summaries row that can be displayed or not, finnally has unique name, that has to be in line with excel names convention. Excel range can be converted to table and back to the regular range.

Power Query, for excel data, accepts only structured tables, both for input and for output. In the latter case it creates structured table automatically. It does not matter how complicated the query is and where the source data come from. If the query returned data to excel worksheet, for excel formulas it is a structured table with all consequences.

In your example the code writes excel formula that refers to the output table. All you have to do is to check the name of the table and add it before column name in square brackets. I'm with Andy: create small table in excel and experiment with the simplest SUM formula for column, columns, whole table, its parts, to see the range syntax.


combo

 

[SkipVought]

1) Edit your formula
2) select the second argument in the SUBTOTAL() function
3) select the table range desired--the CORRECT table reference will appear
4) hit ENTER[sub][/sub]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[dgillz]

Combo,

I am more confused than ever. My output does not have a name, at least not one I ever gave it. When I was happy with query results in SQL Server Mgmnt Studio, I just pasted the code into Power Query. The query has a name, is this what you are referring to?

Macola Software Veteran and SAP Business One Consultant on Training Wheels

Check out our Macola tools:

http://www.gainfocus.biz/exceladdin.html

 

[SkipVought]

@dgillz, did you try my operations? You don't need to know what the name(s) is/are when you point to (select) ranges from within your edited formula or as you build your formula, assuming you use Structured Tables, Named Ranges or other native table features that contain name references.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[combo]

My output does not have a name

Your initial code indicates that you have structured table. Is it automatically formatted by excel, can you see something like this? If any cell in the data range is selected, can you see the table tools tab (table only)? If so, you can find here the table name. The query returned data here, in excel you use its structured references.
Finally, to be sure, as I don't know SSMS, you copied M language query to Power Query?

combo