TEXT-formula in VB Macro fails

[dibudi]

In a VB macro I'm using to transfer values from one sheet to another,
I'm using the following line:

'price column
NewWks.Cells(oRow, "D").Value = .Cells(iRow, iCol).Value

The output of this line is a column with prices, unfortunately these
prices sometimes aren't formatted properly:
235 (is acutally ok)
238,8 (should be 238,80)
299,7171 (should be 299,72)
Since I import the resulting matrix into another application I cannot
use formatting but need to have the correct amount of decimals in the
cell.

I want to use this formula (read this somewhere in a newsgroup) to do
so:
=TEXT([NUMBER];"#.##0,00")

So I changed the line in my VB macro to:
NewWks.Cells(oRow, "D").Value = "=TEXT(" & .Cells(iRow, iCol).Value &
";""#.##0,00"")"

Unfortunately, it doesn't work, I get error 1004 during execution.

Can someone tell me how I can get this working?

 

[Excelenator]

Try changing this

NewWks.Cells(oRow, "D").Value = "=TEXT(" & .Cells(iRow, iCol).Value &
";""#.##0,00"")"

To

NewWks.Cells(oRow, "D").Value
WORKSHEETFUNCTION.TEXT(.Cells(iRow,iCol).TEXT;""#.##0,00""

 

[Excelenator]

Try changing this

NewWks.Cells(oRow, "D").Value = "=TEXT(" & .Cells(iRow, iCol).Value &
";""#.##0,00"")"

To

NewWks.Cells(oRow, "D").Value
WORKSHEETFUNCTION.TEXT(.Cells(iRow,iCol).TEXT;"#.##0,00"

 

[Excelenator]

Try changing this

NewWks.Cells(oRow, "D").Value = "=TEXT(" & .Cells(iRow, iCol).Value &
";""#.##0,00"")"

To

NewWks.Cells(oRow, "D").Value
WORKSHEETFUNCTION.TEXT(.Cells(iRow,iCol).TEXT,"#.##0,00"

 

[dibudi]

When I changed it to
NewWks.Cells(oRow, "D").Value =
WORKSHEETFUNCTION.TEXT(.Cells(iRow,iCol).TEXT;"#.##0,00")
there was an error message about the separator.

I noticed I had to use a comma instead of a semicolon within the
worksheetfunction brackets:
NewWks.Cells(oRow, "D").Value =
WORKSHEETFUNCTION.TEXT(.Cells(iRow,iCol).TEXT,"#.##0,00")

Thanks Excelenator, my macro works fine now!

Excelenator schreef: