Formula inserted by macro doesn't work

[Hubs]

Hi everyone

I'm about to give up! I want to write, in a cell, the "greater or equal" or the "lesser or equal" signs, through a macro.
I've found the Excel formula =UNICAR(8804) for =< (and =UNICAR(8804) for >=). If i write it by hand, it's OK. but my vba is not OK.

ActiveCell.Offset(x,y).Formula R1C1 = "=UNICAR(8804)" results in a #NAME?

I've seen that UNICAR(xxx) neds the "xxx" to be text, and that the cell should be formatted in text, and that formulas should be written in english, so I ended up with the rather not straightforward formula :
ActiveCell.Offset(x,y).FormulaR1C1 = "=UNICAR(DROITE(TEXTE(8804;10);4))"
and
ActiveCell.Offset(x,y).FormulaR1C1 = "=UNICAR(RIGHT(TEXT(8804;10);4))"

Both bring a box with "execution error '1004': Error defined by the application or the object" (I'm translating from the French, but it should be something like that)
If I write the french version by hand in the cell, it's OK.


Question : What should be the proper formula in the macro ?

many thanks for your help

Hubert

 

[Fluff]

How about

ActiveCell.Offset(x,y).Formula = "=UNICAR(8804)"

 

[Hubs]

How about

ActiveCell.Offset(x,y).Formula = "=UNICAR(8804)"[/CODE
[/QUOTE]
I get the #NAME? again

 

[Joe4]

If i write it by hand, it's OK. but my vba is not OK.

If that is true, then try this:
Turn on your Macro Recorder, and record yourself entering the formula into a cell manually (which should work, as you said).
Then turn off the Macro Recorder, and view the code you just recorded. This should show you how VBA wants to see it.
Make the change to your code to match that, and see if that works when you then run the code.

 

[Hubs]

I'm not used to the interface...

Good idea, but I get the #NAME again (I did try that option, too)

I even tried t use ActiveCell.Offset(x.y).Value = "UNICAR(8804)" ...

 

[Fluff]

Just realised you are missing an H it should be UNICHAR

 

[Hubs]

If that is true, then try this:
Turn on your Macro Recorder, and record yourself entering the formula into a cell manually (which should work, as you said).
Then turn off the Macro Recorder, and view the code you just recorded. This should show you how VBA wants to see it.
Make the change to your code to match that, and see if that works when you then run the code.

Bingo ! I need to write ="UNICHAR(8804)"

Many thanks

 

[SQUIDD]

what is unicar

i can only find unichar

 

[Hubs]

Just realised you are missing an H it should be UNICHAR

You are right. I had no clue that UNICAR would need to be translated, too. Many thanks. It works ! Yeepee !

 

[Fluff]

When using VBA to insert a formula you need to use the English version of the function. Another possibility (that I cannot test) is to use your language version of the function but use .FormulaLocal