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

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

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

Ian wrote:
Try this

With ActiveCell
.Value = 1 ' your X
.NumberFormat = "0.0"
End With
End Sub


That works great! Thanks...

Bill

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