0

I am working on building a budget file in Excel:

  • in row 2 I have the months
  • in Column A I have my budget types (categories).

Rather than build a bunch of sumifs in each individual cell, I'm trying to write a custom VBA function for each input (bank, AMEX, VISA etc).

The problem I am running into - without Application.Volatile code, the values do not update automatically in each cell after editing other cells - but when I add Application.Volatile in, it simply records the last value in EVERY CELL, ignoring the budget type (category).

Any ideas how I can build this function?

Function AMEX() As Double
    Application.Volatile
    Dim rngNet As Range
    Dim rngMonth As Range
    Dim rngTypes As Range
    Dim intSearchMonth As Integer
    Dim strSearchType As String

    Set rngNet = shAMEX.Range("E:E")
    Set rngMonth = shAMEX.Range("F:F")
    Set rngTypes = shAMEX.Range("G:G")
    intSearchMonth = shBudget.Cells(2, ActiveCell.Column)
    strSearchType = shBudget.Cells(ActiveCell.Row, 1)

    AMEX = Application.WorksheetFunction.SumIfs(rngNet, _
                                                rngMonth, intSearchMonth, _
                                                rngTypes, strSearchType)
End Function
YowE3K
  • 23,852
  • 7
  • 26
  • 40
belzinga
  • 21
  • 1
  • 2
  • 3
    (a) Don't ever use `ActiveCell` in a UDF. Use `Application.Caller` instead. (That refers to the cell calling the function.) (b) Why don't you pass your ranges and cells to the function as parameters. Then you won't have to mark it `Volatile` and won't slow down your spreadsheet so much. – YowE3K Oct 12 '17 at 00:27
  • Thanks @YowE3K! Sometimes the solution is so simple/obvious you miss it! That would work perfectly. Also thank you for the tip on `Application.Caller` – belzinga Oct 13 '17 at 20:53

0 Answers0