Writing formula in cell gives error although formula is correct

2
votes

I am using VBA to write formulas in an MS Excel sheet. When I copy the formula to the cell manually it works but when I use the line below it gives me a run-time error:

'1004': Application-defined or object-defined error.

ActiveSheet.Cells(Row + 3, Column + i).Formula = "=SUMPRODUCT(--(OFFSET(endResourceNaam;2;0):OFFSET(endResourceNaam;1000;0)=$A" & Row + 1 & ");--(OFFSET(endResourceNaam;2;4):OFFSET(endResourceNaam;1000;4)=$E" & Row + 1 & ");OFFSET(endResourceWeek;2;" & ColumnLetter(Column - 1) & "$1):OFFSET(endResourceWeek;1000;" & ColumnLetter(Column - 1) & "$1))"

The (first in loop) string generated is:

=SUMPRODUCT(--(OFFSET(endResourceNaam;2;0):OFFSET(endResourceNaam;1000;0)=$A6);--(OFFSET(endResourceNaam;2;4):OFFSET(endResourceNaam;1000;4)=$E6);OFFSET(endResourceWeek;2;O$1):OFFSET(endResourceWeek;1000;O$1))

I tested the formula by printing it from the VBA code and than copy/pasta that to the cell. That way I know for certain that the generated string is actually correct. From what I have learned there is some kind of protection build into VBA that makes sure that when I try to write a formula the formula is correct and working. There might be something in that protection that is keeping me from writing the formula to a cell.

Is there any solution for me to write the desired formula to a cell using VBA?

2
vbaexcel
Updated it in my inital post, run-time error 1004. It is usually generated when something is wrong with the formula. For example ActiveSheet.Cells(1,2).Formula = "=2+(3*4" Will generate the same error because a final ) is missing. – hschokker
I see the FormulaArray works with the R1C1 style of cell naming, I will give it a try tomorrow. (msdn.microsoft.com/en-us/library/office/ff837104.aspx) – hschokker
Use commas. I know your system uses a semi-colon list separator but VBA wants EN-US commas when putting a formula into a cell. – user4039065

2 Answers

1
votes

My bet is because it's an array formula, you should use the FormulaArray property.

ActiveSheet.Cells(Row + 3, Column + i).FormulaArray =
1
votes

Jeeped gave the correct answer to my problem, replace the semi-colon list seperators with EN-US commas. Below actually works without a problem:

ActiveSheet.Cells(Row + 3, Column + i).Formula = "=SUMPRODUCT(--(OFFSET(endResourceNaam,2,0):OFFSET(endResourceNaam,1000,0)=$A" & Row + 1 & "),--(OFFSET(endResourceNaam,2,4):OFFSET(endResourceNaam,1000,4)=$E" & Row + 1 & "),OFFSET(endResourceWeek,2," & ColumnLetter(Column - 1) & "$1):OFFSET(endResourceWeek,1000," & ColumnLetter(Column - 1) & "$1))"

I have not tried the FormulaArray method since this already solved my problem.