Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A Better Way (XP)

    I test a variable to determine the day of the week and use code similar to:

    If oType = "SUN" or oType = "Mon" or oType = "TUE" ...... Then blah blah blah

    Is there a better way to write the if statement so I don't have to declare "oType" for each day of the week?

    Thanks,
    John

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: A Better Way (XP)

    Not sure what you are after completely.
    You could use a select case

    you could test the weekday value (1-7) so you could use ranges (instead of Ors)

    If this is not what you are looking for, could you elaborate?

    Steve

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Better Way (XP)

    Steve,

    My goal is to write a more efficient line of code; the less line the better.

    Select Case looks like a better way. I'll me fiddle with it and let you know.

    Thanks,
    John

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: A Better Way (XP)

    You may want to use CHOOSE function? Example: Col A is a series of dates, columns B thru D use these formulas:

    A2: 5/14/2004 (constant)
    B2: =WEEKDAY(A2,1) (returns 6)
    C2: =TEXT(B2,"ddd") (returns "Fri")
    D2: =CHOOSE(WEEKDAY(A2,1),"Sunday","I Don't Like Mondays","Tuesday PM","Wednesday AM","Thursday","TGIF Friday","Sat Nite Fever") (returns "TGIF Friday")

    You can test this by autofilling A22 down a few dozen rows & checking results. Column D should display the text indicated in formula for the day of week for date in Col A. Note that the 2nd arg for WEEKDAY, Return_Type, determines which day of week is defined as "1"; 1 means week starts with Sunday (default in VBA). TEXT function used for test purposes to display day of week as text rather than number. To start week with a day other than Sunday, modify formulas accordingly.

    HTH

  5. #5
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: A Better Way (XP)

    Are you doing this in VBA? I obviously wasn't paying attention. Anyway, the formula would translate to VBA like this example:

    ? Choose(Weekday(Date(),1),"Sunday","I Don't Like Mondays","Tuesday PM","Wednesday AM","Thursday","TGIF Friday","Sat Nite Fever")
    TGIF Friday

    ? Choose(Weekday(#5/17/2004#,1),"Sunday","I Don't Like Mondays","Tuesday PM","Wednesday AM","Thursday","TGIF Friday","Sat Nite Fever")
    I Don't Like Mondays

    The VBA Choose & Weekday functions function pretty much the same way as the equivalent workbook functions. The 2nd arg for Weekday function, 1, also denotes Sunday is first day of week (default). Refer to VBA "Help" for more detail on Choose function.

    HTH

Posting Permissions

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