Cell.Formula failing with single & double variables

Hello,

I can't hardly understand why this is happening to me. Maybe because I'm using a Spanish Office instead of an English one. Anyway, my next piece of code fails.

Sub test()
    Dim Number As Single
    Set Wsheet = ActiveWorkbook.Worksheets(1)
    Number = 6.123456
    Wsheet.Cells(1, 1).Formula = "=CONCATENATE(FIXED(" & Number & ",2),"" N"")"
    Wsheet.Cells(1, 2).Value = Number
    Wsheet.Cells(1, 3).Formula = "=CONCATENATE(FIXED(RC[-1],2),"" N"")"
End Sub

In excel A1 should be =CONCATENATE(FIXED(6,123456;2);" N") ... Assuming in spanish we use , instead of . as decimal mark.
What I'm guetting is =CONCATENATE(FIXED(6;123456;2)" N")... Wich, obviously leads to a #Value error message. Why is excel doing this?

I know I may use another cell to store the value and show the result as in A3. But that's completely lame.

I can't hardly understand why excel behaves like that. Is this behaviour useful anyway? BTW, does anyone know how to avoid this without using a cell to store the value?

NOTE: Checked the code in excel 2002 & 2003.

Hi, I don't see why this should make any difference, but your code did not work for me (Excel 2002) but this did.

Sub test()
    Dim Number As Single, wsheet As Worksheet
    'Set wsheet = ActiveWorkbook.Worksheets(1)
    Set wsheet = Sheets("Sheet1")
    Number = 6.123456
    wsheet.Cells(1, 1).Formula = "=CONCATENATE(FIXED(" & Number & ",2),"" N"")"
    wsheet.Cells(1, 2).Value = Number
    wsheet.Cells(1, 3).Formula = "=CONCATENATE(FIXED(RC[-1],2),"" N"")"
End Sub

Regards Mick

Hi,

I don't know why my code didn't work for you and yours did, but none of them work for me. I'm stuck with the same problem here...

So now I guess this may have something to do with my excel being Spanish version and yours an English one... Wich it's pretty disappointing. I've been trying changing the options at options -> International so decimal marker was a dot with no luck.

Come to think of it, it's going to be hard for those of you with english versions of excel to help me out (if it's a problem of internationalization). Could you at least test my code and grant me it's working flawlessly on your case? And, for those of you without english versions, maybe you know of some workaround (a part of the cell reference trick).

Thanks all!

Xavi

Bump...

Sorry but I'm still stucked with this.