Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Checking for format (XL97/WinNT4)

    Would someone be able to tell me how I check for the format of the content of a cell?

    I have a spreadsheet where I want to work with the various entries in each row, but only if the entry in column A is a date (formatted as dd/mm/yy). The entire column is formatted as a date, but some cells have other text or are empty. I've got as far as this:

    If .Cells(intHRow, 1).value = .... Then

    and in fact I've got the entire contents of the sub apart from how to tell it only to run if column A contains a date! (in fact, the if statement is within a For next loop within the sub, but that's beside the point).

    I'm sure this must be relatively easy, but I've gone through everything I could think of in the Help and haven't managed to track it down! <img src=/S/sigh.gif border=0 alt=sigh width=15 height=15>

    Any help gratefully appreciated!
    Beryl M


  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Checking for format (XL97/WinNT4)

    VBA has a Function, IsDate, which returns True if the value passed to it can be recognized as a date.

    So something like :

    If IsDate(.Cells(intHRow, 1))

    might suit.

    Andrew C

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Checking for format (XL97/WinNT4)

    Thanks for that, Andrew - it worked perfectly!

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Checking for format (XL97/WinNT4)

    You could try looking at .Cells.NumberFormat to see if it contains d, m or y

    StuartR

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Checking for format (XL97/WinNT4)

    Good idea, Stuart - something to remember for another time! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    I'm going with Andrew's 'IsDate' this time, though

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Beryl M


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •