Runtime error 1004 when setting FormulaLocal proerty

Question

I am trying to insert a formula into a cell using the .FormulaLocal property of .Range("F2") on a spreadsheet. For all the neighbouring cells this works fine, but just for this specific cell i get a 1004 runtime error when the VBA script runs. The very strange thing is that the formula I'm putting into this cell is an EXACT copy of the .FormulaLocal property already set in the cell (becaue I want to reset the cell to the original formula before doing some more operations). So how can it be that a formula that works without any kind of problem when I enter it into the cell , all of a sudden causes a runtime error when VBA does the same?

The Formula to be entered is: "=IF(INDEX(CartonList!$H:$H,MATCH(PolyList!$A2,CartonList!$L:$L,0))>0,INDEX(CartonList!$H:$H,MATCH(PolyList!$A2,CartonList!$L:$L,0)),"")"

Which in FormulaLocal since I have Swedish language pack installed is: "=OM(INDEX(CartonList!$H:$H;PASSA(PolyList!$A2;CartonList!$L:$L;0))>0;INDEX(CartonList!$H:$H;PASSA(PolyList!$A2;CartonList!$L:$L;0));"")"

And the statement in my VBA script causing the runtime error is:

With ThisWorkbook.Sheets("PolyList")

.

. **Other .FormulaLocal statements that do not cause runtime error**

.

.Range("F2").FormulaLocal = "=OM(INDEX(CartonList!$H:$H;PASSA(PolyList!$A2;CartonList!$L:$L;0))>0;INDEX(CartonList!$H:$H;PASSA(PolyList!$A2;CartonList!$L:$L;0));"")"

.

.

.

End With

I have tried setting .Formula and .Formula2 with the non local version of this formula as well, which did not help. The other formulas that don't cause problems also reference the same sheets "PolyList" and "CartonList" without problems. Only thing different with this particular formula is that it contains an IF statement, but should that really matter?

Answer 1

Thanks for your reply.

I can't upload a copy of this file becasue it contains sensitive business data, sorry. I had to do a workaround and structure the data in such a way that the IF-statement in this particular formula could be removed, and then it worked just fine so the problem is sort of solved by workaround. But it still seems strange to me that having an IF statement causes runtime error when assigning through VBA, when the formula itself works ok on the spreadsheet entered in exactly the same syntax.

Answer 2

Could you share a test file to test code?

For sharing a sample workbook, upload to OneDrive or any other cloud drives. Then post a link here.

Why a sample file is important for troubleshooting. How to do it. - Microsoft Community