none
VBA:How to get sum of cells when I know other cell value RRS feed

  • 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?
    Wednesday, January 29, 2014 11:55 PM

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 add-ins & workbooks)

    • Edited by James Cone Sunday, October 30, 2016 10:57 PM
    Thursday, January 30, 2014 1:34 AM
  • 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?

    Thanks

    EDIT:

    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
    Thursday, January 30, 2014 6:34 PM
  • Re:  huh?

    Typical function syntax...

    Function SomeName() As Double
      Dim X As Double
      X = Application.WorksheetFunction.Sum(1, 2, 3, 4)
      SomeName = X                     '<<<<
    End Function

    '---
    Jim Cone

    Thursday, January 30, 2014 7:23 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

    Thursday, January 30, 2014 10:23 PM