We help IT Professionals succeed at work.

SUBTOTAL in Excel using VBA

Mezczyzna
Mezczyzna asked
on
8 Comments
1 Solution
157 Views
Last Modified: 2018-09-07
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

View Solution Only

NorieAnalyst Assistant
CERTIFIED EXPERT

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
CERTIFIED EXPERT
Most Valuable Expert 2018
Awarded 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Roy CoxGroup Finance Manager
CERTIFIED EXPERT

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

Overview of Excel tables
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
CERTIFIED 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
CERTIFIED EXPERT

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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.

View Solution