Format range

[vbaNewby]

Hello I have a range variable named rng.

I set it. I then want to format it with DD/MM/YYYY

I'm getting a type mismatch using this:

'set range
Set rng = ....

rng = Format(rng, "DD/MM/YYYY")

Help!

 

[VoG]

Try

Rng.NumberFormat = "DD/MM/YYYY"

 

[vbaNewby]

Try

Rng.NumberFormat = "DD/MM/YYYY"

Hi Vog,

Thanks for the reply. This runs without error but it does not change the format of the range.

What i am doing is setting the range, use Rng.NumberFormat = "DD/MM/YYYY"
, then rng.Copy, change sheets and paste in the range. On my pasted sheet though, its not formatted...?

 

[vbaNewby]

Try

Rng.NumberFormat = "DD/MM/YYYY"

VoG,

Also after the pasting of the range, I look at the cell and it is formatted as a custom format with DD/MM/YYYY

But yet it still looks like this: Jan 1, 2010.

Is this because the original contents of the rng are not date formatted and just simple text???

 

[VoG]

I think that you have text dates, not Excel dates. You cal check like this

=ISNUMBER(A1)

which will return True for a date, False for text.