PC Review


Reply
Thread Tools Rating: Thread Rating: 2 votes, 2.00 average.

Changing Cells format using VBA

 
 
=?Utf-8?B?VG9wcGVycw==?=
Guest
Posts: n/a
 
      10th Jun 2005
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.
>
>
>

 
Reply With Quote
 
 
 
 
Amir
Guest
Posts: n/a
 
      10th Jun 2005
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.


 
Reply With Quote
 
 
 
 
Amir
Guest
Posts: n/a
 
      10th Jun 2005
It works!
Thank you very much!

Regards,
Amir.

"Toppers" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>>
>>
>>



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
VBA: format, Format or FORMAT =?Utf-8?B?QkVFSkFZ?= Microsoft Excel Programming 7 30th Oct 2006 07:09 PM
want format cells alignment not format cells font style =?Utf-8?B?SmVhbm5pZSBCZWFu?= Microsoft Excel Misc 2 10th Feb 2006 10:31 AM
Cells won't convert to number format, even after format/cells/num. =?Utf-8?B?c2NvdHRy?= Microsoft Excel Misc 5 12th Apr 2005 11:02 PM
Copy a formula to a range of cells via VB6 using .Range(Cells(row,col), Cells(row,col)).Formula= statement Kevin Microsoft Excel Programming 7 5th Oct 2004 08:11 PM
Changing format in selected cells (VBA) Roderick O'Regan Microsoft Powerpoint 2 26th Mar 2004 01:48 PM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:30 PM.