Reference Excel Totals Rows by Column Name?

Get help with using AutoHotkey and its commands and hotkeys
jaybro
Posts: 2
Joined: 29 Mar 2016, 09:17

Reference Excel Totals Rows by Column Name?

08 Feb 2018, 15:27

Suppose your have a table in Excel with the totals row showing. You know the column headings and would like to apply a formula to a specific total cell using nothing more than the column heading text and table objects.

The "Get_Excel" function (found on the forums) gives me a solid handle on the workbook. And, this code works... it's just... ugly:

Code: [Select all] [Download] GeSHi © Codebox Plus

xl := Excel_Get()
t := xl.ActiveSheet.ListObjects(1)
t.ShowTotals := 1

colName := "A Known Column Heading Name"
xl.Range(t.Name . "[[#Totals],[" . colName . "]]").Formula := "=SUBTOTAL(109,[colName])" ; this is the line I'm trying to improve upon


This is working but I can't seem to piece this together a more elegant solution using table objects.

I wish this would work but it doesn't:

Code: [Select all] [Download] GeSHi © Codebox Plus

t.ListColums(colName).TotalsRowRange.Formula := "=SUBTOTAL(109,[colName])"


Any ideas? Again, I'm not trying to use the column's index number here... I have a column name in hand and want to apply the formulas based on know column names.
FanaticGuru
Posts: 918
Joined: 30 Sep 2013, 22:25

Re: Reference Excel Totals Rows by Column Name?

08 Feb 2018, 16:48

jaybro wrote:I wish this would work but it doesn't:

Code: [Select all] [Download] GeSHi © Codebox Plus

t.ListColums(colName).TotalsRowRange.Formula := "=SUBTOTAL(109,[colName])"


Any ideas? Again, I'm not trying to use the column's index number here... I have a column name in hand and want to apply the formulas based on know column names.
You don't normally set formulas for the total row in a table. It has predefined formula that you should use. They are what you chose from the dropdown menu when you click on one of these cells. You can set the formulas directly and not use the predefined but then you are kind of working against the grain of tables.

Code: [Select all] [Expand] [Download] (Untitled.ahk)GeSHi © Codebox Plus

I used actual names to identify the table and column but you can also use index numbers.

FG
Hotkey Help - Help Dialog for Currently Running AHK Scripts

AHK Startup - Consolidate Multiply AHK Scripts with one Tray Icon

Google Search, Dictionary, Thesaurus - Quickly Get Information from Specific Web Resources

[Function] Timer - Create and Manage Timers
jaybro
Posts: 2
Joined: 29 Mar 2016, 09:17

Re: Reference Excel Totals Rows by Column Name?

09 Feb 2018, 16:36

Thanks, FG!

I didn't know about TotalsCalculation object.

Here's what I came up for a cleaner range reference (which I'm happy with) to manage the Totals row:

ListColumns("Column1").Total.Address

Code: [Select all] [Download] GeSHi © Codebox Plus

xl := Excel_Get()
t := xl.ActiveSheet.ListObjects(1)
t.ShowTotals := 1

xl.Range(t.ListColumns(""Avg WrapUp per Call"").Total.Address).Formula := ""=IFERROR((SUM([[#Totals],[Wrap-Up Time]],[[#Totals],[Wrap-Up (NRC 14)]]) / [[#Totals],[Total Phone Contacts]]),0)"

ExitApp

Return to “Ask For Help”

Who is online

Users browsing this forum: Grez and 49 guests