Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Format a Cell from VBA
I've got a macro that does a bunch of things and at the end puts a number value
in a cell. I would like to write out that value to be displayed in a fixed format with one decimal digit. Somehow it *almost* works for me. I've tried to use a statement like: ActiveCell.Offset(0,0) = FORMAT(X,"0.0") When X is some kind of long decimal like 1.33333 it works nicely and shows "1.3" in the cell as expected. When X is an even integer like 1.0 though, it only shows "1" in the cell. Yet from the spreadsheet I can click to set the number of digits afterwards and it works as expected. In addition to the format "0.0" I've tried "#.#" with the same result. I also tried "#.#_" and it does force the correct format, but of course appends the character on the end which I don't want. What's the necessary incantation here? I've been unable to find anything useful in the Help system or in Walkenbach's book -- doubtlessly I haven't been able to guess the proper search term for the answer I want. Thanks... Bill |
#2
|
|||
|
|||
try
activecell.numberformat = "0.0" activecell = x "Bill Martin" wrote: I've got a macro that does a bunch of things and at the end puts a number value in a cell. I would like to write out that value to be displayed in a fixed format with one decimal digit. Somehow it *almost* works for me. I've tried to use a statement like: ActiveCell.Offset(0,0) = FORMAT(X,"0.0") When X is some kind of long decimal like 1.33333 it works nicely and shows "1.3" in the cell as expected. When X is an even integer like 1.0 though, it only shows "1" in the cell. Yet from the spreadsheet I can click to set the number of digits afterwards and it works as expected. In addition to the format "0.0" I've tried "#.#" with the same result. I also tried "#.#_" and it does force the correct format, but of course appends the character on the end which I don't want. What's the necessary incantation here? I've been unable to find anything useful in the Help system or in Walkenbach's book -- doubtlessly I haven't been able to guess the proper search term for the answer I want. Thanks... Bill |
#3
|
|||
|
|||
Try this
With ActiveCell .Value = 1 ' your X .NumberFormat = "0.0" End With End Sub -- Ian -- "Bill Martin" wrote in message ... I've got a macro that does a bunch of things and at the end puts a number value in a cell. I would like to write out that value to be displayed in a fixed format with one decimal digit. Somehow it *almost* works for me. I've tried to use a statement like: ActiveCell.Offset(0,0) = FORMAT(X,"0.0") When X is some kind of long decimal like 1.33333 it works nicely and shows "1.3" in the cell as expected. When X is an even integer like 1.0 though, it only shows "1" in the cell. Yet from the spreadsheet I can click to set the number of digits afterwards and it works as expected. In addition to the format "0.0" I've tried "#.#" with the same result. I also tried "#.#_" and it does force the correct format, but of course appends the character on the end which I don't want. What's the necessary incantation here? I've been unable to find anything useful in the Help system or in Walkenbach's book -- doubtlessly I haven't been able to guess the proper search term for the answer I want. Thanks... Bill |
#4
|
|||
|
|||
Ian wrote:
Try this With ActiveCell .Value = 1 ' your X .NumberFormat = "0.0" End With End Sub That works great! Thanks... Bill |
#5
|
|||
|
|||
That does it! Thanks...
Bill ----------------- bj wrote: try activecell.numberformat = "0.0" activecell = x "Bill Martin" wrote: I've got a macro that does a bunch of things and at the end puts a number value in a cell. I would like to write out that value to be displayed in a fixed format with one decimal digit. Somehow it *almost* works for me. I've tried to use a statement like: ActiveCell.Offset(0,0) = FORMAT(X,"0.0") When X is some kind of long decimal like 1.33333 it works nicely and shows "1.3" in the cell as expected. When X is an even integer like 1.0 though, it only shows "1" in the cell. Yet from the spreadsheet I can click to set the number of digits afterwards and it works as expected. In addition to the format "0.0" I've tried "#.#" with the same result. I also tried "#.#_" and it does force the correct format, but of course appends the character on the end which I don't want. What's the necessary incantation here? I've been unable to find anything useful in the Help system or in Walkenbach's book -- doubtlessly I haven't been able to guess the proper search term for the answer I want. Thanks... Bill |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference Cell in custom format???? | Excel Discussion (Misc queries) | |||
Why does my format keep changing in the cell? | Excel Discussion (Misc queries) | |||
how do I format a cell reference to move as source changes | Excel Worksheet Functions | |||
Show Blank is cell value=0 but count as a zero in sum. How to format this cell ? | Excel Worksheet Functions | |||
How do I unhide the cell format function in Excel 2000 | Excel Worksheet Functions |