Problem using vba code to insert a formula to certain cell

[Juhanen II]

Hi all,

I have problems vith my VBA code. I try to put a formula to a certain cell using this code, but I get application defined or object defined error:

The weird thing is, that if I type the same formula itself to a cell, it works, so the problem shouldn't be with the formula:

On the other hand I tried to replace the formula in my VBA code with simpler one, like this one and that worked aswell:

So what am I missing here, where is the mistake?

-Juhanen-

[StephenR]

Works for me if I substitute , for ; - is that definitely the separator according to your settings?

[Juhanen II]

Yes. ";" is the right separator for me.
Weird that it works for you...

[StephenR]

Have you tried varying the active cell when you run the code? Why can't you specify the address you want?

[Richard Schollar]

Hi

Change the ; to , and it will work for you too

Richard

[Phil_V]

Works here too, again having to replace ";" with "," though.

[Juhanen II]

Hey guys,

Varying the active cell didn't work.
BUT changing ; to , really did work!
I'm amazed, because my separator really is ;

So conclusion:

in VBA code using ";" --> doesn't work
in VBA code using "," --> works
in cell formula using ";" --> works
in cell formula using "," --> doesn't work

I still don't get it why is that, but atleast my VBA code works now =)

Thank you all for solving my strange problem.

-Juhanen-

[StephenR]

Richard, do you know why that is?

[Richard Schollar]

Stephen

I think it is probably just an oversight by Excel's/Office's designers so that even though the system separators can be changed (ie , to ; ) they never considered - or bothered to implement - this change in the VBE and how VBA code is interpreted.

Richard

[StephenR]

Thanks Richard. I suppose we can let them off that one.

[Juhanen II]

I wouldn't let them off that easily though, causes me troubles

Btw. the reason we use ";" as a separator instead of "," here (Finland) is that we use "," for desimals. So "two point 5" is 2,5 here

Anyways, thanks for all the help again, keep up the good work.

-Juhanen-