Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Using Subtotal function in VBA


Reply
 
Thread Tools Display Modes
  #1  
Old 05-06-2008, 06:14 AM
Abbasi Abbasi is offline
Newcomer
 
Join Date: Aug 2003
Location: Islamabad
Posts: 11
Default Using Subtotal function in VBA


Hello everybody

I am trying to read from a worksheet cell, programmatically, which is displaying a value of the Subtotal function.

I used the
Code:
Cells(R, C).Value
statement. Unfortunately, it does not return the value of the formula.

Then I tried to embed the formula within my VBA procedure as below:
Code:
Var = Application.WorksheetFunction.Subtotal(3, V4:V450)
; which results in error
or
Code:
Var = Application.WorksheetFunction.Subtotal(3, Range("V4:V450")
; which returns 0.

I desperately need help to get around the problem. Please help.

Regards
Reply With Quote
  #2  
Old 05-06-2008, 06:30 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,400
Default

Hi Abbasi,

I suspect your problem with examples (1) and (3) is that you are not qualifying the parent worksheet of the range and your code is not "looking at" the worksheet you want it to.

Here's assuming that the target worksheet resides in the same workbook as your code....

For your example (1), if your formula is in cell E8 on a worksheet named "Sheet1" then this would work:
Code:
MsgBox Worksheets("Sheet1").Cells(8, "e").Value      'OR .....Cells(8, 5).Value
Similarly in example (3) you would use something like:
Code:
Sub test()
    Dim mySubtotal As Double
    
    mySubtotal = Application.WorksheetFunction.Subtotal(3, Worksheets("Sheet1").Range("V4:V450"))
    
    MsgBox mySubtotal
End Sub
And I think that example shows how the syntax you used in Example (2) is incorrect.

Why are you using the SUBTOTAL function for this - I assume you are working on a filtered range?

I hope that helps -
Colin
__________________
RAD Excel Blog

Last edited by Colin Legg; 05-06-2008 at 06:40 AM.
Reply With Quote
  #3  
Old 05-06-2008, 06:47 AM
Abbasi Abbasi is offline
Newcomer
 
Join Date: Aug 2003
Location: Islamabad
Posts: 11
Default

Thank you very much, Colin, for your attention. Let me look into your point. I may get back, if necessary.

Yes, I am reading from the filtered range.

Have a great time and regards.
Reply With Quote
  #4  
Old 05-06-2008, 06:49 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,400
Default

Quote:
Originally Posted by Abbasi View Post
Thank you very much, Colin, for your attention. Let me look into your point. I may get back, if necessary.

Yes, I am reading from the filtered range.

Have a great time and regards.
Sure thing, Abbasi. If you're still having troubles then, when you post back, it might be helpful to us if you could attach a sample workbook for us to look at. That way we'll be able to identify the problem more quickly for you!

Good luck!


===========
EDIT:
===========

I'm suspecting that this thread is a follow on from the question you posted here:
http://www.excelforum.com/showthread.php?t=643685

Just FYI - instead of filtering and using SUBTOTAL....
If you want to get a count for all unique usernames --> use a pivottable
if you want to get a count for one unique username in the list --> use COUNTIF.

===========
===========
__________________
RAD Excel Blog

Last edited by Colin Legg; 05-06-2008 at 07:21 AM.
Reply With Quote
  #5  
Old 05-07-2008, 11:12 PM
Abbasi Abbasi is offline
Newcomer
 
Join Date: Aug 2003
Location: Islamabad
Posts: 11
Default

Hello again!

Mr. Colin correctly pointed me out the mistake I was committing. Further to the same, I am trying to write the Subtotal formula into the cell as below:

Code:
ActiveSheet.Range("A3").Formula = _
        "=Application.WorksheetFunction.Subtotal(3, Worksheets(ActiveSheet).Range(chr(34) & A4:A & chr(34) & str(LastRow))"
The avove syntax reports "Application-defined" error.

If Colin is reading this post, I exclusively request him to shed more light. I will appreciate help from all the other wizards as well.

Last edited by Abbasi; 05-07-2008 at 11:44 PM.
Reply With Quote
  #6  
Old 05-07-2008, 11:44 PM
shg shg is offline
Junior Contributor

* Expert *
 
Join Date: May 2008
Posts: 297
Default

Are you trying to insert a formula or a result?

If a formula on the active sheet, then perhaps like this:

Code:
    With Worksheets(SrcWrksht)
        Range("A3").Formula = _
        "=Subtotal(3, " & .Range("A4", .Cells(LastRow, "A")).Address(, , , True) & ")"
    End With
If a result, then
Code:
    With Worksheets(SrcWrksht)
        Range("A3").Value = _
        Application.WorksheetFunction.Subtotal(3, .Range("A4", .Cells(LastRow, "A")))
    End With

Last edited by shg; 05-07-2008 at 11:54 PM. Reason: Bug!
Reply With Quote
  #7  
Old 05-07-2008, 11:56 PM
Abbasi Abbasi is offline
Newcomer
 
Join Date: Aug 2003
Location: Islamabad
Posts: 11
Default

Very Good Morning and thank you very much, shg, for your help. It works fine, but with just a minor issue - it posts absolute cell addresses (with $). I need to programmatically copy this formula into all the columns of the same row.

Would you kindly let me know, how to post the formula with relative cell addresses, please!
Reply With Quote
  #8  
Old 05-08-2008, 09:49 AM
shg shg is offline
Junior Contributor

* Expert *
 
Join Date: May 2008
Posts: 297
Default

Good morning, Abbasi.

Take a look at Help for the Addresss property of the Range object. The first two arguments control whether the result is row and/or column absolute.

Last edited by lebb; 05-08-2008 at 12:23 PM.
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
 
 
-->