Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Visual Basic for Excel (Microsoft Excel 2000)

    Need a routine that would make two different cells on a Worksheet to state 100% if the Day is either Saturday or Sunday. The day will start in cell D1 and continue to cell AM1. (IE.... Sat, Sun, Mon, ETC ETC ETC..... for the whole month). The Cells that would state 100% range from D33 to AM33. ANy Suggestions?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Visual Basic for Excel (Microsoft Excel 2000)

    D1:AM1 contains 36 cells, so that is more than a month.

    Do you really need VBA code? You could put the following formula in D33, then fill right to AM33 and format as a percentage:

    <code>=IF(WEEKDAY(D1,2)>5,1,"")</code>

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visual Basic for Excel (Microsoft Excel 2000)

    Hi Hans, Regarding your reply, I only need the 100% to be filled in on saturday and sundays only. The rest of the days are auto generated. Will this still work?

  4. #4
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visual Basic for Excel (Microsoft Excel 2000)

    Hans one more question... in your reponse what exactly does the D1,2 > 5,1 Mean. I mean I know what the D1 is i'm not sure about the ,2 and the 5,1 could you please explain this so that I finally understand and do not have to ask this ridiculous question again. Thanks

  5. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visual Basic for Excel (Microsoft Excel 2000)

    Hans's formula, in words, translates to:

    If the value of D1 (using return_type 2 - Numbers 1 (Monday) through 7 (Sunday)) is greater than 5 (i.e. is either 6 or 7)
    then the result of the formula is 1
    otherwise it is an empty string "" .

    Alan

  6. #6
    2 Star Lounger
    Join Date
    Apr 2004
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Visual Basic for Excel (Microsoft Excel 2000)

    Thank you GentleMan it works like a charm.

Posting Permissions

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