VBA - SUMPRODUCT with SumIf With Dynamic Ranges

[dmekwunye]

I know that in order to use Sumproduct in VBA I need to have the results set as value. For example, here's a code that I have been using and it works great:

With ActiveSheet.Range("P" & LR + 1)
.Formula = "=SUMPRODUCT($N$16:N" & LR & ", $P$16:P" & LR & ")/SUM($N$16:N" & LR & ")"
.Value2 = .Value2

I now have a formula that I'm having trouble with. It's a SUMPRODUCT with SumIf. I tried using the code above and adding the SUMIF piece, but it did not work. Here's what the current formula looks like:

Range("V" & lastrow + 11).Select
ActiveCell.Formula = "'=SUMPRODUCT(($A$11:$A$3798=""RSU"")*$AH$11:$AH$3798,$N$11:$N$3798)/SUMIF($A$11:$A$3798,""RSU"",$AH$11:$AH$3798)"
Range("V" & lastrow + 11).Select

This does not work. I can add an apostrophe to the formula and it will be entered as text in the cell. I then just have to remove the apostrophe to get the formula to work. I'm hoping that there is a way to skip the apostrophe step. Here's what the code looks like with the apostrophe:

Range("V" & lastrow + 11).Select
ActiveCell.Formula = "''=SUMPRODUCT(($A$11:$A$3798=""RSU"")*$AH$11:$AH$3798,$N$11:$N$3798)/SUMIF($A$11:$A$3798,""RSU"",$AH$11:$AH$3798)"
Range("V" & lastrow + 11).Select


Any help/suggestion is much appreciated.

Thanks,

 

[pgc01]

Hi
Welcome to the board

The only thing I see wrong is that the first formula starts with a single quote. This means that the formula is entered as text in the cell.

Also, why does the second formula start with 2 single quotes?

Just remove the single quotes and it should work.

Make sure the cell format is not text before writing the formula.

 

[dmekwunye]

I originally added the single quote because I kept getting an error. By adding the single quote, it would enter the formula as text and then I just had to manually remove the single quote and copy down. The 2 double quotes in the second example was an error.

I just removed the single quotes and there is no more error. The code works fine now.

Thanks,

De Anna

 

[dmekwunye]

Follow-up question, for the following formula:

ActiveCell.Formula = "=SUMPRODUCT(($A$11:$A3798=""NQ"")*$W$11:$W$3798,$H$11:$H3798)/SUMIF($A$11:$A$3798,""NQ"",$W$11:$W3798)"

What if row A3798, H3798, W3798 is dynamic and can change depending on the spreadsheet. How can I update the formula to find the last row in each column?

Thanks,

De Anna