Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
vlookup formula fails to return data search item is text | Excel Worksheet Functions | |||
Sumif Fails w Date as Text | Excel Discussion (Misc queries) | |||
Formula fails if cells text format | Excel Discussion (Misc queries) | |||
Formula fails in text formatted cell | Excel Worksheet Functions | |||
Macro fails without VB help files | Excel Programming |