Asked by:
VBA:How to get sum of cells when I know other cell value
Question

Hello everybody
I am new to VBA but have good knowledge of dot net, SQL and so. Recently my boss handed me an Excel file where he needs some calculations. In SQL it would be very easy but I can't figure out how to do it in Excel.
Table example:
MODNAME ACTIVITY PRICE
MOD1 0001 0.00
MOD1 0002 0.00
MOD1 0003 0.00
MOD2 0001 0.00
MOD2 0002 0.00
MOD2 0003 0.00
And all I need to do is something like:
select sum(price) as TotalPrice from TableName where modname = 'mod2' and activity in('0001','0003')
Google pointed me to AutoFilters, so I wrote a Sub which visually works.
Code:
Dim rangeFound As String
Sub FindValue()
Range("A1").AutoFilter Field:=1, Criteria1:="mod2"
Range("A1").AutoFilter Field:=2, Criteria1:=Array("0003", "0004", "0007", "0008"), Operator:=xlFilterValues
rangeFound = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Address
MsgBox (rangeFound)
Range("A1").AutoFilter
End Sub
But I can't figure out how to use this range to get the sum.
Any help is appreciated.
Thanks in advance
Raj
BTW, if I run the sub directly, it gives me right range of visible cells but if I call the same Sub from a function like:
Function FindValue2() As String
Call FindValue
End Function
it always gives me range $A$1. Why so?
All replies

Re: sum range with multiple criteria
'
Sub FindValueR1()
Dim rangeFound As String '<<< moved
Range("A1").AutoFilter Field:=1, Criteria1:="mod2"
Range("A1").AutoFilter Field:=2, Criteria1:=Array("0003", "0004", "0007", "0008"), Operator:=xlFilterValues
rangeFound = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Address
MsgBox Application.WorksheetFunction.Sum(Range(rangeFound)) '<<< changed
Range("A1").AutoFilter
End Sub
OR
Sub FindValueR2()
Dim rangeFound As Excel.Range
Range("A1").AutoFilter Field:=1, Criteria1:="mod2"
Range("A1").AutoFilter Field:=2, Criteria1:=Array("0003", "0004", "0007", "0008"), Operator:=xlFilterValues
Set rangeFound = Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Cells
MsgBox Application.WorksheetFunction.Sum(rangeFound)
Range("A1").AutoFilter
Set rangeFound = Nothing
End Sub
'
Jim Cone
Portland, Oregon USA
https://goo.gl/IUQUN2 (Dropbox)(free & commercial excel addins & workbooks)
 Edited by James Cone Sunday, October 30, 2016 10:57 PM

Thank you for your reply Jim
General SUM works, I know. But I needed to get the sum of a certain column which I fixed now by using the intersection.
Sub now works as I want.
But my issue now is that if I change the Sub into a Function, it stops working even though the code is exactly same.
When the same code is called as a function from the worksheet, my Excel sheet gets filtered but only by the second field, the result I get is always zero. And the filter is not turned off at the end of the function.
Same code works perfectly from the Sub, so I must be missing something very basic.
I need to have this into a function so I can call it from the worksheet with arguments.
Any ideas?
ThanksEDIT:
Problem is most probably with rangeFound because from the function it always return $A$1 and not $A$1:$E$9 for example as it returns from the Sub
 Edited by Madhra Thursday, January 30, 2014 6:47 PM


Jim,
Of course I am returning the value, otherwise it won't be a function. And as I said, the value is always zero.
I put breakpoints and checked the code step by step. Code works differently in the function.
I will ignore your previous answer with respect. Please, before you answer again, respect that I am a programmer for many years (though new to VBA).
Thank you