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
Who is Participating?

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) "
``````
0

Manager, 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

Author 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

VBA ExpertCommented:
Is there a formula in RC[-3]?
0

Author 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

VBA 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

Author 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

Author 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

Author Commented:
This have provided the results I was hopeing for after I included "IFERROR" to the formula.