TEXT-formula in VB Macro fails
 

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?

TEXT-formula in VB Macro fails
 

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""

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56788

TEXT-formula in VB Macro fails
 

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"

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56788

TEXT-formula in VB Macro fails
 

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"

--
Excelenato

-----------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...fo&userid=3676
View this thread: http://www.excelforum.com/showthread.php?threadid=56788

TEXT-formula in VB Macro fails
 

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:

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


------------------------------------------------------------------------
Excelenator's Profile: http://www.excelforum.com/member.php...o&userid=36768
View this thread: http://www.excelforum.com/showthread...hreadid=567888