Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2013
    Posts
    163

    Unanswered: Sumproduct Function in VBA

    Dear Seniors,

    I had created a Array sum product formula with multiple conditions and it works fine. 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

    {=SUMPRODUCT(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.
    Thanks and Regards
    R. Vadivelan

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    If I interpret you formula correctly I don't think you need SumProduct, it could just be nested IFs ie

    =IF(((CPYFORIFD>0)*(CPYFORIFD<=C$1)),1,IF(((CPYFOR IFA>0)*(CPYFORIFA<=C$1)),0.8,IF(((CPYFORIFR>0)*(CP YFORIFR<=C$1)),0.6,0)))*(CPYWF)

    If this is so, then VBA could look something like this??

    Code:
    Function VBA_Function() As Single
        Select Case True
            Case Is = Range("CPYFORIFD") > 0 And Range("CPYFORIFD") <= Range("C1")
                VBA_Function = 1 * Range("CPYWF")
            Case Is = Range("CPYFORIFA") > 0 And Range("CPYFORIFA") <= Range("C1")
                VBA_Function = 0.8 * Range("CPYWF")
            Case Is = Range("CPYFORIFR") > 0 And Range("CPYFORIFR") <= Range("C1")
                VBA_Function = 0.6 * Range("CPYWF")
            Case Else
                VBA_Function = 0
        End Select
    End Function
    HTH

    MTB

  3. #3
    Join Date
    Oct 2013
    Posts
    163
    Hi Mike,

    Thanks for your reply. Actually my one is a array formula. I had tried copy your code in a module, but returns error as #VALUE!. Kindly advice me If I had done wrongly.

    Also I want the name range should be dynamic one. (i.e.) I want to use this formula in different worksheets. I want the range should be selected whenever I am executing this formula.

    In mean time I will work with my file by using If condition instead of sum product and update you.
    Thanks and Regards
    R. Vadivelan

  4. #4
    Join Date
    Oct 2013
    Posts
    163
    Hi Everybody,

    Could you please help me how to write a Array Formula in VBA code.

    So that I will try to create my formula.
    Thanks and Regards
    R. Vadivelan

  5. #5
    Join Date
    Oct 2013
    Posts
    163
    Hi Everybody,

    Could you please help me how to write this Array Formula in VBA code.

    Thanks and Regards
    R. Vadivelan
    Thanks and Regards
    R. Vadivelan

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •