0

I am writing a macro which requires me to insert a formula to a number of consecutive cells in a column.

For row number 10, I must insert

=IF(D10="","",IFERROR(SUMIFS(E:E,D:D,D10,A:A,A10,B:B,B10),"E"))

For row number 11, I must insert

=IF(D11="","",IFERROR(SUMIFS(E:E,D:D,D11,A:A,A11,B:B,B11),"E"))

And so on... However, the following hardcoded version (I did it just for trial) returns the 'Expected end of statement' error.

Cells(11, "F").Formula = "=IF(D11="","",IFERROR(SUMIFS(E:E,D:D,D11,A:A,A11,B:B,B11),"E"))"

Is there any way to avoid this error and insert the formula by using rowNum variable? I want to increment the row number (rowNum = rowNum + 1) after inserting the formula. Also, if possible, I would like to use constant ERROR instead of hardcoding "E" in the formula.

Any help is much appreciated!

  • You need to double up the quotes in the formula, see the linked duplicate. – BigBen Oct 27 '20 at 16:44
  • You can write a formula with relative references to an entire range, and Excel will update the reference for each row, e.g. `Range("F10:F100").Formula = "=IF(D10="""","""",IFERROR(SUMIFS(E:E,D:D,D10,A:A,A10,B:B,B10),""E""))`. – BigBen Oct 27 '20 at 16:45
  • 1
    Hi Big Ben, Thanks a lot for helping me out! My macro works totally fine now... –  Oct 28 '20 at 03:20

0 Answers0