Sumif with autofilter vba

[Av8tordude]

Dim My Range as Range

Set MyRange = Range("Q7", Range("Q7").End(xlDown)).SpecialCells(xlCellTypeVisible)

WorksheetFunction.SumIf(MyRange, ">0")

I have this code sums value greater than zero. I'm using an autofilter and I would like to know how I can get the results if the rows are filtered?

Thanks

 

[Alex Blakenburg]

Try this:

Dim Answer As Double
Answer = Evaluate("SUMPRODUCT(SUBTOTAL(109,OFFSET(Q$7,ROW(MyRange)-ROW(Q$7),0))*(MyRange>0))")

 

[Av8tordude]

Thank you for the reply, however, I'm getting a type mismatch error.

 

[Alex Blakenburg]

Thank you for the reply, however, I'm getting a type mismatch error.

Can you post your code and do an XL2BB of your data or at least Column mentioned in your code which was Q in the initial code snippet.

 

[Fluff]

How about

Dim MyRange As Range
Dim Ans As Double
Set MyRange = Range("Q7", Range("Q" & Rows.Count).End(xlUp))
Ans = Evaluate("SUMPRODUCT(SUBTOTAL(109,OFFSET(Q$7,ROW(" & MyRange.Address & ")-ROW(Q$7),0))*(" & MyRange.Address & ">0))")

 

[Av8tordude]

Works great Fluff...Thank you

Question...why use Evaluate and not Worksheet function?

 

[Fluff]

why use Evaluate and not Worksheet function?

It's easier IMO.

 

[Alex Blakenburg]

Question...why use Evaluate and not Worksheet function?

I actually tried the Worksheet function initially but while the Evaluate function lets you basically use the same Excel function you use for your original test build in Excel, the Worksheet function requires a lot of moving parts. ie

• You need to know which functions you are using in the formula that require the Application.WorksheetFunction in front of it.
  As near as I can figure out in these formulas its the SumProduct and the SubTotal functions
• Functions that have VBA equivalents don't seem to be available and you need to re-engineer them to use the VBA function.
  This seems to be the case with the Offset & Row functions
  [Excel allows Row(Range), VBA needs Range().Row]

Needless to say I could not get it working and the Evaluate function was sooo much simpler.

 

[Av8tordude]

I actually tried the Worksheet function initially but while the Evaluate function lets you basically use the same Excel function you use for your original test build in Excel, the Worksheet function requires a lot of moving parts. ie

• You need to know which functions you are using in the formula that require the Application.WorksheetFunction in front of it.
  As near as I can figure out in these formulas its the SumProduct and the SubTotal functions
• Functions that have VBA equivalents don't seem to be available and you need to re-engineer them to use the VBA function.
  This seems to be the case with the Offset & Row functions
  [Excel allows Row(Range), VBA needs Range().Row]

Needless to say I could not get it working and the Evaluate function was sooo much simpler.

Alex...I'm am greatly appreciative of your detail explanation. I actually tried the worksheet function too, but I got stuck when I kept getting an error on ROW. Needless say I came here for help. I never used evaluate before, but now I have a new tool in my toolbox