• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

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
0
user2073
Asked:
user2073
  • 5
  • 3
1 Solution
 
Swapnil NirmalCommented:
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
 
NorieCommented:
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
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
NorieCommented:
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
 
NorieCommented:
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now