Changing Cells format using VBA

[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.

[Toppers]

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" wrote:

> 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.

"Toppers" <[email protected]> wrote in message
news:[email protected]...
> 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" wrote:
>
>> 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.
>>
>>
>>