×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Issue with Subtotals on a Table

Issue with Subtotals on a Table

Issue with Subtotals on a Table

(OP)
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:

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:

www.gainfocus.biz/exceladdin.html

RE: Issue with Subtotals on a Table

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

combo

RE: Issue with Subtotals on a Table

(OP)
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:

www.gainfocus.biz/exceladdin.html

RE: Issue with Subtotals on a Table

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

RE: Issue with Subtotals on a Table

Quote (dgillz)

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

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

---- Andy

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

RE: Issue with Subtotals on a Table

(OP)
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:

www.gainfocus.biz/exceladdin.html

RE: Issue with Subtotals on a Table

Quote (dgillz)

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

RE: Issue with Subtotals on a Table

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Issue with Subtotals on a Table

(OP)
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:

www.gainfocus.biz/exceladdin.html

RE: Issue with Subtotals on a Table

@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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

RE: Issue with Subtotals on a Table

Quote (dgillz)

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close