Customized Sumproduct Function in Excel VBA

V

velu130486

Board Regular
Joined
Apr 4, 2014
Messages
99
Dear Seniors,

I am new user to VBA. I had created a Array sum product formula with multiple conditions. Now I would like to get help from this forum to create the same formula in Excel VBA. Presently I am using named ranges in my formula. But I would like to use the formula by selecting the dynamic ranges.

Here is the formula I am using to calculate my result based on the Date in C1, DEPT Value in D1

{=SUMPRODUCT((DEPT=D1)*IF(((CPYFORIFD>0)*(CPYFORIFD<=C$1)),1 ,IF(((CPYFORIFA>0)*(CPYFORIFA<=C$1)),0.8,IF(((CPYF ORIFR>0)*(CPYFORIFR<=C$1)),0.6,0)))*(CPYWF))}

where us CPYFORIFD, CPYFORIFA, CPYFORIFR are Named ranges which contains Dates & CPYWF is a Number. I am browsing the internet for last 2 weeks but I could not crack the code. Please help me to sort out this issue and this will help me a lot.

My Inputs will be CPYFORIFD, CPYFORIFA, CPYFORIFR (Dates), CPYWF (Double) and DEPT (String)




Thanks and Regards
R. Vadivelan
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
What do you mean by "create the same formula in Excel <ACRONYM title="visual basic for applications">VBA</ACRONYM>"? If you want to enter the formula in a cell the macro recorder will give you the syntax.
 
Upvote 0
Hi Andrew,

Yes I want to create the same formula in VBA. I had used the macro recorder and found the syntax. however I am not able to convert it to VBA Function. And the second thing is my input will be dynamic. i.e whenever I use the function i will give the inputs of Dates like excel sum-product.

Thanks in Advance for your help
Regards
R. Vadivelan
 
Upvote 0
Andrew Poulsom said:
What did you try when converting to a VBA function exactly?
Click to expand...

Hi Andrew,

The Following is the code I had created in VBA but I know it is wrong and I am unable to correct the same. As said earlier, I am a new user to VBA so I need your help to sort out this issue. This formula is very much useful for my project.

Code: 
Function ExecSumProduct(CutoffDate As Date, DEPT As String, CPYPLANIFD As Range, CPYPLANIFA As Range, CPYPLANIFR As Range, CPYWF As Range) As Integer

    Selection.FormulaArray = _
        "=SUMPRODUCT((DEPT=D1)*IF(((CPYPLANIFD>0)*(CPYPLANIFD<=C$1)),1,IF(((CPYPLANIFA>0)*(CPYPLANIFA<=C$1)),0.8,IF(((CPYPLANIFR>0)*(CPYPLANIFR<=C$1)),0.6,0)))*(CPYWF))"
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"

End Function

I was used to work with formulas, now the worry is formula is becoming very long and it is increasing the chance of errors.

Thanks in Advance for your help
Regards
R. Vadivelan
 
Upvote 0
Hi Andrew,

I am calling that function similar to excel formulas.
i.e =ExecSumProduct

Thanks in Advance for your help
Regards
R. Vadivelan
 
Upvote 0
Hi Andrew,

Formatting is not a problem. My Objective is to create a user defined function (UDF) which will produce the same result of my formula

i.e. When I call the ExecSumProduct function it has to calculate the following formula

{=SUMPRODUCT((DEPT=D1)*IF(((CPYFORIFD>0)*(CPYFORIFD<=C$1)),1,IF(((CPYFORIFA>0)*(CPYFORIFA<=C$1)),0.8,IF(((CPYFORIFR>0)*(CPYFORIFR<=C$1)),0.6,0)))*(CPYWF))}

Here the values in D1 & C1 are variable remaining inputs are constant.

My objective is to instead of typing the long formula in all cells, in order to avoid any mistakes I would like to create a UDF.

Please let me know for any clarifications required.

Thanks in Advance for your help
Regards
R. Vadivelan
 
Upvote 0
Maybe try:

Code: 
Function ExecSumProduct(DEPT As Range, CutoffDate As Range)
    With Application.Caller.Parent
        ExecSumProduct = .Evaluate("=SUMPRODUCT((DEPT=" & DEPT.Address & ")*IF(((CPYFORIFD>0)*(CPYFORIFD<=" & CutoffDate.Address & ")),1,IF(((CPYFORIFA>0)*(CPYFORIFA<=" & CutoffDate.Address & ")),0.8,IF(((CPYFORIFR>0)*(CPYFORIFR<=" & CutoffDate.Address & ")),0.6,0)))*(CPYWF))")
    End With
End Function
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
How can I allocate task across multiple departments and periods?
Replies
0
Views
81
charltondav
C
B
  • Question
VBA to show and change list of names depending on selection
Replies
7
Views
471
mumps
mumps
B
  • Question
Help with VBA code to copy/paste into another workbook then email
Replies
1
Views
234
bh24524
B
drom
  • Question
Excel's formatcells/Number/Custom >1 or =0
Replies
3
Views
59
SanjayGMusafir
SanjayGMusafir
A
  • Question
VBA Highlight cell if contains specific text
Replies
4
Views
157
DanteAmor
DanteAmor

Forum statistics

Threads
1,215,063
Messages
6,122,935
Members
449,094
Latest member
teemeren

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top