CODEWITHSUNDEEP
vbaexcelfunctionrange
Hagelqvist
Hagelqvist

Reputation: 1

Excel VBA Function, multiple user defined ranges

I've been trying out the public function in excel to create my own formulas that me and my co-workers could use, however after a lot of googling I can't seam to solve this one.

What I'm trying to do in this example is to have a formula that the user can call upon by typing "=SumCells()" in a cell and then select the range to sum with a set of citeras that they also should select from the excel.

On top of this I would like the option where I could look at the Date criteria and if the Date is equal to a date from another cell + an integer from another cell that will add to x months to it, it will summarize that as well. Data and criterias

So once I use the formula it would look something like this. Formula

Public Function SumCells(RangeToSum As Range, CriteraRange_1 As Range, Critera_1 As Range, CriteraRange_2 As Range, Critera_2 As Range, MonthsToAdd As Integer)

SumCells = Application.SumIfs(Range(RangeToSum), _
         Range(CriteraRange_1), Range(Critera_1).Value, _
         Range(CriteraRange_2), "=" & DateAdd("m", MonthsToAdd, Range(Critera_2).Value)) 
End Function

Appreciate any help and best regards!

Upvotes: 0

Views: 278

Answers (1)

user4039065
user4039065

Reputation:

Remove the Range 'wrappers' from the range objects.

Public Function SumCells(RangeToSum As Range, _
                         CriteraRange_1 As Range, Critera_1 As Range, _
                         CriteraRange_2 As Range, Critera_2 As Range, MonthsToAdd As Integer)

    SumCells = Application.SumIfs(RangeToSum, _
             CriteraRange_1, Critera_1.Value, _
             CriteraRange_2, DateAdd("m", MonthsToAdd, Critera_2.Value))

End Function

enter image description here

Upvotes: 1

Related Questions