Formula not working when used with VBA

[LGMan]

Hi, as usual I've inherited a workbook which I have been adding some VBA to, however when trying to add a formula into VBA i'm getting a resultant #NAME? error in the cell

ActiveCell.FormaulR1C1 = "=F(VALUE(M2)<100,LEFT(M2,1)&CHAR(46)&RIGHT(M2,1),LEFT(M2,1)&CHAR(46)&MID(M2,2,1)&CHAR(46)&RIGHT(M2,1))"

If I paste the formula directly into the cell it works. It's purpose is to put decimal between the digits, so 77 becomes 7.7 and 199 becomes 1.9.9 and so on.

All help greatly appreciated

 

[zelgar]

Is this supposed to be an IF statement? If so, then you forgot the I after the equal sign
"=IF(VALUE(M2)<100,LEFT(M2,1)&CHAR(46)&RIGHT(M2,1),LEFT(M2,1)&CHAR(46)&MID(M2,2,1)&CHAR(46)&RIGHT(M2,1))"

 

[LGMan]

Dang, that was a paste error, my formula does have the =IF rather than an =F.
I've noticed that the cell value shows
=IF(VALUE('M2')<100,LEFT('M2',1)&CHAR(46)&RIGHT('M2',1),LEFT('M2',1)&CHAR(46)&MID('M2',2,1)&CHAR(46)&RIGHT('M2',1))

So the Cell Ref of M2 is showing as 'M2'

 

[LGMan]

Ok, the moral of the story is go away have a break, then look at it again!

Replace the Cell Refs with the correct RC[-1]

so my formula is...
"=IF Value([highlight #F57900]RC[-1][/highlight])<100,Left..... and so on.

Sorry I reached for help when the answer was staring at me :-(

 

[PHV]

Or even simplier:
ActiveCell.Formula = "=IF(VALUE(M2)<100,LEFT(M2,1)&CHAR(46)&RIGHT(M2,1),LEFT(M2,1)&CHAR(46)&MID(M2,2,1)&CHAR(46)&RIGHT(M2,1))"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[zelgar]

I don't know if this would be an interest to you, but you could possibly use a Number Format to create spaces between your numbers with something like the following
Selection.NumberFormat = "# # # # # #"
this would result in the following for
1 = 1
12 = 1 2
123 = 1 2 3
1234 = 1 2 3 4
12345 = 1 2 3 4 5

By doing this, the data in the cells are still numbers so you can still add/subtract/multiply/divide the data.

 

[SkipVought]

Since you posted in the VBA forum, I suppose that a VBA solution is apropos.

Function(s as string)
Dim I as integer

S = ""

For I = 1 to len(trim([M2]))
    S = s & "."
Next

S = left(s,len(s)-1)
End function

Use as any spreadsheet function.