SUBTOTAL in Excel using VBA

Mezczyzna
Mezczyzna used Ask the Experts™
on
Hello,

Trying to enter a formula =SUBTOTAL(109,[BookCreditAmount])[/u]), using VBA.

Tried the following, (and more), and it doesn't work.

'    Range("O" & lRowDump + 1, "O" & lRowDump + 1).Formula = "=SUBTOTAL(109,[BookCreditAmount])"
'    Selection.FormulaR1C1 = "=SUBTOTAL(109,[BookCreditAmount])"
'    Range("N" & lRowDump + 1, "N" & lRowDump + 1).Formula = "=SUBTOTAL(109,[BookDebitAmount])"
'    Range("N" & lRowDump + 1, "N" & lRowDump + 1).Select
'    Selection.FormulaR1C1 = "=SUBTOTAL(109,[BookDebitAmount])"
'    ActiveCell.Formula = "=SUBTOTAL(109,[BookCreditAmount])"

Your help is appreciated.
Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
NorieAnalyst Assistant

Commented:
What is [BookCreditAmount] supposed to refer to?

If it's a field/column in a table then you would also need the table name.
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
If BookCreditAmount is a column in a Table called Table1, try this...
Range("O" & lRowDump + 1).Formula = "=SUBTOTAL(109,Table1[BookCreditAmount])"

If BookCreditAmount is a named range, try this...
Range("O" & lRowDump + 1).Formula = "=SUBTOTAL(109,BookCreditAmount)"
Roy CoxGroup Finance Manager

Commented:
Wouldn't it be simpler to use an Excel Table with the Totals feature, no VBA required.

Overview of Excel tables
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
MezczyznaERP Specialist

Author

Commented:
Hello,
No it wouldn't be simpler.

I should provide more information. Sorry.
The data comes through ODBC from a SQL database view per accounting period. The [BookCreditAmount] is the header name of the column in the SQL query 'dump'. There is [BookDebitAmount] as well.
I don't know how to remove this column naming by ODBC from Excel 2013 / 2016. Honestly it drives me bananas.
So, the formula has to be entered each time the data is refreshed by changes in accounting period, company or account number.

I'll try Norie's and Neeraj's suggestion since this is a 'table'.
I let Excel to enter the sum formula on top of the column and this is what it gave me:
=SUM(Table_Query_from_SQLForEpic34[BookCreditAmount])

Do you know if the query (table name) is hard coded into to spreadsheet? I hope the name will not change.
But I guess I can insert a name each time right after refresh.

Thank you.
MezczyznaERP Specialist

Author

Commented:
Thank you. It worked.
You don't give points anymore?
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
You're welcome!
Yes, the points system has been changed. Now you get a slider to award the bonus points.
Roy CoxGroup Finance Manager

Commented:
This adds subtotals to a Table(ListObject). When I use it remembers the previous settings for the Table totals.

ActiveSheet.ListObjects(1).ShowTotals = True

Open in new window

MezczyznaERP Specialist

Author

Commented:
Thank you Roy, I will try that later. For now this task is done.

I added a few lines to delete the formula at the end, and enter it again with each new dump.
It works and they can reconcile transactions.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial