SUBTOTAL in Excel using VBA

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.
MezczyznaERP SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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)"

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Wouldn't it be simpler to use an Excel Table with the Totals feature, no VBA required.

Overview of Excel tables
Price Your IT Services for Profit

Managed service contracts are great - when they're making you money. Yes, you’re getting paid monthly, but is it actually profitable? Learn to calculate your hourly overhead burden so you can master your IT services pricing strategy.

Download eBook
MezczyznaERP SpecialistAuthor 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 SpecialistAuthor Commented:
Thank you. It worked.
You don't give points anymore?
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You're welcome!
Yes, the points system has been changed. Now you get a slider to award the bonus points.
Roy CoxGroup Finance ManagerCommented:
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 SpecialistAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.