## Use vba to add sumproduct formula's to range

VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

### Use vba to add sumproduct formula's to range

I need to (using vba) populate cells F2:F18 with the following formula, where each one looks at the relevant row (C, D, B 2:18)

=IF(C2="",0,SUMPRODUCT((((Rejections!\$D\$301:\$D\$10000=C2)*(Rejections!\$B\$301:\$B\$10000=D2)))*((Rejections!\$J\$301:\$J\$10000))))

How is the best way to achieve this?

Thanks for any help.
Nathan
There's no place like home.....

HansV
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

### Re: Use vba to add sumproduct formula's to range

Range("F2:F18").Formula = "=IF(C2="""",0,SUMPRODUCT((((Rejections!\$D\$301:\$D\$10000=C2)*(Rejections!\$B\$301:\$B\$10000=D2)))*((Rejections!\$J\$301:\$J\$10000))))"

The row reference will be adjusted automatically.
Best wishes,
Hans

VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

### Re: Use vba to add sumproduct formula's to range

You are a legend! :Thankyou:

You actually gave me the wrong answer...... but as usual, that's because I asked the wrong question!
Nathan
There's no place like home.....

rory
5StarLounger
Posts: 818
Joined: 24 Jan 2010, 15:56

### Re: Use vba to add sumproduct formula's to range

So it was actually the right answer to the wrong question?
Regards,
Rory

VegasNath
5StarLounger
Posts: 1185
Joined: 24 Jan 2010, 12:02
Location: Wales, UK.

### Re: Use vba to add sumproduct formula's to range

No, I figured out the right answer after wasting another 30 minutes of my life..... grrrr
Nathan
There's no place like home.....