Changing Cells format using VBA

Discussion in 'Microsoft Excel Programming' started by Guest, Jun 10, 2005.

  1. Guest

    Guest 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" 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.
    >
    >
    >
     
    Guest, Jun 10, 2005
    #1
    1. Advertisements

  2. Guest

    Amir Guest

    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, Jun 10, 2005
    #2
    1. Advertisements

  3. Guest

    Amir Guest

    It works!
    Thank you very much!

    Regards,
    Amir.

    "Toppers" <> wrote in message
    news:...
    > 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, Jun 10, 2005
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. wuming
    Replies:
    6
    Views:
    1,790
    wuming
    Jul 9, 2004
  2. Kevin
    Replies:
    7
    Views:
    628
    Kevin
    Oct 5, 2004
  3. Guest
    Replies:
    10
    Views:
    275
    colofnature
    Jul 19, 2006
  4. Guest

    Copy works using A1:L1 Format but Not Cells(..) Format

    Guest, Oct 26, 2006, in forum: Microsoft Excel Programming
    Replies:
    2
    Views:
    152
    Guest
    Oct 26, 2006
  5. Guest

    VBA: format, Format or FORMAT

    Guest, Oct 27, 2006, in forum: Microsoft Excel Programming
    Replies:
    7
    Views:
    833
    Guest
    Oct 30, 2006
Loading...

Share This Page