Inserting formula into cell through vba command

[VBAlearner4work]

Hi everyone,
I'm getting quite confused with a VBA code that i'm trying to implement.

I have a formula which consists out of several concatenated parts, which needs to be assigned to a specific cell:

.Range("C1").Formula="INDEX(E" & Cstr(i) & ":AA" & Cstr(i) & ";1;MATCH($D$17;$E$2:$AA$2;0))"

in which i is an iterator(1,2,3...).

i get an error trying to run this, and have pinpointed the error to the part:

.Range("C1").Formula="INDEX(E"

which gives the same error.

any help is appreciated


kind regards,

 

[Peter_SSs]

Welcome to the MrExcel board!

Try replacing those semicolons with commas (even thouigh your version may use semicolons in worksheet formulas)

.Range("C1").Formula="INDEX(E" & Cstr(i) & ":AA" & Cstr(i) & ";1;MATCH($D$17;$E$2:$AA$2;0))"

 

[steve the fish]

What is the error? What is the value of i when that line executes?

 

[steve the fish]

Welcome to the MrExcel board!

Try replacing those semicolons with commas (even thouigh your version may use semicolons in worksheet formulas)

.Range("C1").Formula="=INDEX(E" & Cstr(i) & ":AA" & Cstr(i) & ";1;MATCH($D$17;$E$2:$AA$2;0))"

It also needs another equals sign.

 

[Peter_SSs]

It also needs another equals sign.

Good catch.

 

[VBAlearner4work]

Hi everyone,
the semicolons definitely were the reason of the error.
("i" is just any integer number)
The code now manages to put the formula string into the cell , however it doesn't evaluate immediately.
I have to manually select the cell, click inside the text box and press enter to convert the actual function
how do i solve this?

Thanks a lot already.
kind regards.

 

[VBAlearner4work]

solved it, vba has trouble when using different languages, as soon as i turned everything to english, it works fine.

 

[Peter_SSs]

Glad you have got it sorted.