Format Cells - quick question

BenNV · May 22nd, 2002, 02:35 AM

Morning, I have set a particular cell to format to 4 decimal places but it doesn't seem to work unless I click into the cell and click out of the cell. How can I format automatically?

sykes · May 22nd, 2002, 02:42 AM

Hi Ben
I suppose you've tried the following have you?:
Range("B2").NumberFormat = "0.0000"

Sykes

BenNV · May 22nd, 2002, 03:05 AM

No luck with that Sykes.

sykes · May 22nd, 2002, 03:15 AM

Ben
Perhaps you could be a bit more specific. If you used the above formula, and replaced B2 with your own cell, what happens etc etc,

Kindest

Sykes

BenNV · May 22nd, 2002, 03:42 AM

Sykes, I use the formula u specify and run a macro which copies the data from Sheet1 to Sheet2. I set sheet2 as active and specify your formula. But once I copy the data from Sheet1 to Sheet2 it still pastes the data as 11 decimal places rather than 4 decimal places.

Vas · May 22nd, 2002, 03:48 AM

Try

FirstRange.Copy

'Switch sheet

SecondRange.PasteSpecial xlPasteValues

sykes · May 22nd, 2002, 03:56 AM

Ben
I think that as you copy the data over last, the format from the original data cell is being copied over as well.
What I suggest is to copy the data over first into the cell, and THEN run the format formula.

Sykes

BenNV · May 22nd, 2002, 04:12 AM

Still no luck - only seems to work when I click into and out of the cell.

sykes · May 22nd, 2002, 05:01 AM

Ben
I've started a new workbook, formatted A2 on sheet 2 as number to 11 dec places, and put a figure to 11 dec places in A2, sheet 2.

I've written in a macro as follows:

Sub formatb2()

Worksheets("sheet1").Select
Range("B2").Value = Worksheets("sheet2").Range("A2")
'above should be all one line
Range("B2").NumberFormat = "0.0000"

End Sub

on running the macro, the number comes up corretly to 4 DP.
If you've still got probs suggest posting your code up on the board.

Sykes

_________________
....and the meek shall inherit the earth...
(but not the mineral rights!)

[ This Message was edited by: sykes on 2002-05-22 04:13 ]

May 22nd, 2002, 03:15 AM	#4
sykes Board Regular Join Date: May 2002 Location: Cornwall,England Posts: 1,273	Ben Perhaps you could be a bit more specific. If you used the above formula, and replaced B2 with your own cell, what happens etc etc, Kindest Sykes __________________ Sykes Vista / Xl2007

May 22nd, 2002, 03:48 AM	#6
Vas Board Regular Join Date: May 2002 Location: Gothenburg, Sweden Posts: 74	Try FirstRange.Copy 'Switch sheet SecondRange.PasteSpecial xlPasteValues __________________ /Niklas Jansson

May 22nd, 2002, 03:56 AM	#7
sykes Board Regular Join Date: May 2002 Location: Cornwall,England Posts: 1,273	Ben I think that as you copy the data over last, the format from the original data cell is being copied over as well. What I suggest is to copy the data over first into the cell, and THEN run the format formula. Sykes __________________ Sykes Vista / Xl2007

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 22nd, 2002, 02:35 AM	#1
BenNV Board Regular Join Date: Mar 2002 Posts: 160	Morning, I have set a particular cell to format to 4 decimal places but it doesn't seem to work unless I click into the cell and click out of the cell. How can I format automatically?

May 22nd, 2002, 02:42 AM	#2
sykes Board Regular Join Date: May 2002 Location: Cornwall,England Posts: 1,273	Hi Ben I suppose you've tried the following have you?: Range("B2").NumberFormat = "0.0000" Sykes

May 22nd, 2002, 03:05 AM	#3
BenNV Board Regular Join Date: Mar 2002 Posts: 160	No luck with that Sykes.

May 22nd, 2002, 03:42 AM	#5
BenNV Board Regular Join Date: Mar 2002 Posts: 160	Sykes, I use the formula u specify and run a macro which copies the data from Sheet1 to Sheet2. I set sheet2 as active and specify your formula. But once I copy the data from Sheet1 to Sheet2 it still pastes the data as 11 decimal places rather than 4 decimal places.

May 22nd, 2002, 04:12 AM	#8
BenNV Board Regular Join Date: Mar 2002 Posts: 160	Still no luck - only seems to work when I click into and out of the cell.

May 22nd, 2002, 05:01 AM	#9
sykes Board Regular Join Date: May 2002 Location: Cornwall,England Posts: 1,273	Ben I've started a new workbook, formatted A2 on sheet 2 as number to 11 dec places, and put a figure to 11 dec places in A2, sheet 2. I've written in a macro as follows: Sub formatb2() Worksheets("sheet1").Select Range("B2").Value = Worksheets("sheet2").Range("A2") 'above should be all one line Range("B2").NumberFormat = "0.0000" End Sub on running the macro, the number comes up corretly to 4 DP. If you've still got probs suggest posting your code up on the board. Sykes _________________ ....and the meek shall inherit the earth... (but not the mineral rights!) [ This Message was edited by: sykes on 2002-05-22 04:13 ]