Relative formula's by VBA

In youur code change this line

Target.Offset(0, 72).ActiveCell.FormulaR1C1 = "=SUM(OFFSET(R1C5,COUNTA(C[-72])-1,,,65))"

By this

Target.Offset(0, 72).ActiveCell.Formula = "=SUMPRODUCT($BV$2:$EH$2,E2:BQ2)"

Originally Posted by kvsrinivasamurthy

In youur code change this line




By this

Thanks for the input. When trying this I get an error message: "Application-defined or object defined error".
Debugging shows this line of code:

Please Login or Register  to view this content.

highlighted.

In your line you have to remove R1C1. It should br like this.

Target.Offset(0, 72).ActiveCell.Formula = "=SUMPRODUCT($BV$2:$EH$2,E2:BQ2)"

Originally Posted by kvsrinivasamurthy

In your line you have to remove R1C1. It should br like this.

Thanks. I've figured out the formula, using "ActiveCell" messed up the process. Instead:

Please Login or Register  to view this content.

gets the formula in there.

The problem I'm now facing is this: When a new row of data is put into the sheet, the formula is static as "=SUMPRODUCT($BV$2:$EH$2,E2:BQ2)", yet I need it to be relative. So if data is imported to cell A10, I need the formula to refer to E10:BQ10, if data is imported to A20 the range needs to be E20:BQ20 and so on.

Any advice?

Got it working! R1C1 helped me out in the end, thanks to kvsrinivasamurthy for pointing me in the right direction with the syntax.

Final sub:

Please Login or Register  to view this content.