How can I add the value in a cell to a Sumproduct formula in vba.

How do I add the value of a cell located at RC[-3] on the left of the formula below in the same sheet.

ActiveCell.Offset(0, 1).Range("A1").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((SED=RC[-10])*((1& SEC & SEB)+0>=EDATE(R2C2,-11))*((1& SEC & SEB)+0<R[-3]C[-9]),SEI)"
    Selection.NumberFormat = "[=0]"""";0.0"

I have tried a couple process without any success could some assist me Please.
Codes-Test.docx
user2073Asked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Do you just want to add that one value to the result of the formula?

This code will do that.
ActiveCell.FormulaR1C1 = _
        "=RC[-3] + SUMPRODUCT((SED=RC[-10])*((1& SEC & SEB)+0>=EDATE(R2C2,-11))*((1& SEC & SEB)+0<R[-3]C[-9]),SEI) "

Open in new window

0
 
Swapnil NirmalManager, Audit AnalyticsCommented:
Highlighted part below is not a number. hence the error. it retruns true. the problem is in formula not in vba code

    ActiveCell.FormulaR1C1 = _
        "=SUMPRODUCT((SED=RC[-10])*((1& SEC & SEB)+0>=EDATE(R2C2,-11))*((1& SEC & SEB)+0<R[-3]C[-9]),SEI)"
0
 
user2073Author Commented:
The above code works if the cell RC[-3] has a value if the cell has not value the formula returns #VALUE.
Can you solve this part of the problem?

THANKS
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NorieVBA ExpertCommented:
Is there a formula in RC[-3]?
0
 
user2073Author Commented:
No "RC[-3]" it is just the first stage in the formula see below:

ActiveCell.FormulaR1C1 = _
        "=RC[-3] + SUMPRODUCT((SED=RC[-10])*((1& SEC & SEB)+0>=EDATE(R2C2,-11))*((1& SEC & SEB)+0<R[-3]C[-9]),SEI) "

This formula returns #VALUE if "RC[-3]" is empty, the formula works when the cell has a value.

Any solution would be great.
THANKS
0
 
NorieVBA ExpertCommented:
Does the formula work without RC[-3]?

What is in RC[-3] and what is the active cell when you run the code?
0
 
user2073Author Commented:
YES, the formula works perfectly for the addition of the 11 months using "EDATE(R2C2-11)" my only issue is that I'm trying to add into the formula the value for the current month. The current month is located at "RC[-3]" on the same row as the formula. The rest of the data is called from another worksheet.

The activecell is Column "K" and the cell that holds the value for "RC[-3] (sample "2.3") is in column "H" on the same row.

I have also tried to use "IFERROR" this works also except when I sort in descending all the blank cells preceed the largest value on the top of the list. I would like the "Blank or Empty" cells to placed at bottom of the list.

THANKS
0
 
user2073Author Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for user2073's comment #a38415822

for the following reason:

The &quot;IFERROR&quot; has worked to solve the problems. Thanks for your assistence it is appreciated.<br /><br />THANKS
0
 
user2073Author Commented:
This have provided the results I was hopeing for after I included "IFERROR" to the formula.

Thanks for your assistence.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.