Me: Doctor, Doctor. It hurts when I do this.
Dr: Don't do that!

(Well, that's the lesson I learned!)

Myrna Larson wrote:

I think it's interesting, too. Evidently if you try to apply a Null format to
a single cell, it just ignores you. If you try to apply it to a multiple cell
selection, it clears the format. Strange...

On Tue, 12 Oct 2004 19:58:25 -0500, Dave Peterson wrote:

xl2002 (sp2):

I formatted a cell as number, 2 decimals & comma and ran this with just that
one
cell selected:

Debug.Print ActiveCell.NumberFormat
ActiveCell.NumberFormat = Null
Debug.Print ActiveCell.NumberFormat

I got this in the immediate window:
#,##0.00
#,##0.00

I changed to a cell formatted as general and got this:
General
General

I formatted A1 as number, 2 decimals & comma and selected a1:a10 (a2:a10 were
still General):

I got this:
Null
General

All the cells in the selection were changed to general.

Well, I thought it was slightly interesting <bg.





Myrna Larson wrote:

I'm running XL2002 (XP). I don't get an error.

If I had, I wouldn't have posted the comment. As I tried to imply, I
thought
it strange that there was no error.

Evidently it doesn't give an error for the OP, either. I believe he asked
why
it didn't work. I assumed that meant only that the formats weren't copied,
and
he would have said so if it produced a run-time error.

BTW, another solution to this is to Edit/Copy the range, followed by 2
PasteSpecial statements, one that copies the values, the other that copies
the
formats.

On Tue, 12 Oct 2004 05:57:27 -0700, keepITcool
wrote:

correction:
xlXP and xl2003 throw error.
xl97 accepts

selection.numberformat = Null


keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Peter T" <peter_t@discussions wrote:

assigning NULL or a mixed bag of numberformats throws exception in
all versions on my PC (as expected)

Like Myrna, assigning a number format of NULL does not error for me.

Selection.NumberFormat = Null

With identical formats this does nothing, ie original format retained.
With mixed number formats all are changed to General. But no error.

Regards,
Peter


"keepITcool" wrote in message
...
To OP:
If you're coding for xl2000+ then you could use:

rngSrc.Copy
rngDst.PasteSpecial xlPasteValuesAndNumberFormats

To Myrna:
assigning NULL or a mixed bag of numberformats throws exception in
all versions on my PC (as expected)

i did note that assigning EMPTY clear formats... (not surprisingly)




keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


Myrna Larson wrote:

PS: The interesting thing is that assigning a number format of
NULL, as your code is probably doing, doesn't cause a run-time
error.

On Tue, 12 Oct 2004 00:12:28 -0500, Myrna Larson
wrote:

I expect the underlying problem is that in your source range, the
number format varies from one cell to another. In that situation,
the .Numberformat property returns NULL. It doesn't return an array
of formats for each cell in the range.

To demonstrate: put some data in, say A1:A3, and set different
number formats for at least one of the cells. Then select those
cells, in the VBE immediate window type ? Selection.Numberformat.
You'll see that it prints NULL.

If the formats vary, and you want to replicate them, you'll have to
do it a cell at a time, or at least do it in "chunks" of cells, all
of which have the same number format.


On Mon, 11 Oct 2004 23:54:45 -0500, SteveS
wrote:


I must be missing something obvious here... I am trying to use a
range copy to copy values and formats from a series of worksheets.
The values come over fine, but the number formats do not.

What is the trick to copy number formats? The vba is:
With sh.Range(sh.Cells(FirstRow, FirstCol), sh.Cells(shLast,
LastCol)) DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).Value = .Value
DestSh.Cells(Last + 1, 1).Resize(.Rows.Count, _
Columns.Count).NumberFormat = .NumberFormat
End With

Thanks,
Steve







--

Dave Peterson