CODEWITHSUNDEEP
vbaexcel
kpp
kpp

Reputation: 826

Excel VBA Run-time error 1004 when inserting or value formula into cell

I got the run-time 1004 error when It try to insert a formula into a cell

Range("B64").Value = "=INDEX(AK7:AK123;G74;1)"

//I also tried
Range("B64").Formula = "=INDEX(AK7:AK123;G74;1)"
//And
Range("B64").FormulaR1C1 = "=INDEX(AK7:AK123;G74;1)"

But this gives the error. If I try inserting a number or a regular string like "test" it does work, but like this it doesn't. Im new to VBA and im wondering why this would give a problem since it would never in languages im used to.

Upvotes: 7

Views: 17517

Answers (1)

user4039065
user4039065

Reputation:

Inserting a formula with VBA requires that you use EN-US standards like,

Range("B64").Formula = "=INDEX(AK7:AK123, G74, 1)"

... or use the regional formula attribute like,

Range("B64").FormulaLocal = "=INDEX(AK7:AK123; G74; 1)"

You may have to also change INDEX to the regional equivalent. The latter is necessary when you have a system with regional settings that do not use the EN-US standard of a comma for a list separator.

see Range.FormulaLocal Property (Excel) for more information.

Upvotes: 16

Related Questions