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 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:
Var = Application.WorksheetFunction.Subtotal(3, V4:V450); which results in error
or
Var = Application.WorksheetFunction.Subtotal(3, Range("V4:V450"); which returns 0.

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

Regards

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:

MsgBox Worksheets("Sheet1").Cells(8, "e").Value 'OR .....Cells(8, 5).Value


Similarly in example (3) you would use something like:

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

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.

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.

===========
===========

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:


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.

Are you trying to insert a formula or a result?

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

With Worksheets(SrcWrksht)
Range("A3").Formula = _
"=Subtotal(3, " & .Range("A4", .Cells(LastRow, "A")).Address(, , , True) & ")"
End With

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

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!

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.