Changing Cells format using VBA

[Guest]

Hi,

Some examples ..

Record a macro and change the cell formats to see the various options.

Range("C1").Select
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss" 'Date as10/06/2005
Selection.NumberFormat = "dd/mm/yyyy hh:mm" No seconds displayed
Selection.NumberFormat = "dd-mmm-yyyy hh:mm:ss" 'Date as 10-Jun-2005
Range("D1").Select
Selection.NumberFormat = "@" 'Text

HTH

 

[Amir]

Hi!

I have some questions about cells formatting:

1. How can I make sure that the format of the data in a certain range/Cell
is Date?
I mean, If I have strings that represent dates, such as "14/5/2006 12:26" in
all the rows in column B, How can I use VBA to make sure that Excel treats
that as date value (and not numbers or text) when i sort the worksheet ?
I know it is possible to do this manually by right clicking the mouse,
choosing "Format Cells", pressing on "Date", then selecting the date type,
but how can I do this with VBA?

2. How I can I control the specific date type (e.g. 14/3/01 versus 14.3.01)
using VBA?

3. How can I do the same but for text format (instead of date)?

Kind regards,
Amir.

 

[Amir]

It works!
Thank you very much!

Regards,
Amir.

Hi,

Some examples ..

Record a macro and change the cell formats to see the various options.

Range("C1").Select
Selection.NumberFormat = "dd/mm/yyyy hh:mm:ss" 'Date as10/06/2005
Selection.NumberFormat = "dd/mm/yyyy hh:mm" No seconds displayed
Selection.NumberFormat = "dd-mmm-yyyy hh:mm:ss" 'Date as 10-Jun-2005
Range("D1").Select
Selection.NumberFormat = "@" 'Text

HTH