+ Reply to Thread
Results 1 to 5 of 5

Create VBA to distribute rates and exclude weekend

  1. 06-24-2014, 05:19 AM #1
    laramfarhat
    laramfarhat is offline
    Registered User
    Join Date
    06-24-2014
    Location
    uae
    MS-Off Ver
    2007
    Posts
    4

    Create VBA to distribute rates and exclude weekend

    Hi
    I have to create VBA according to these criterias and I would be thankful if you can help:
    I have a list of 22 rates that i need to distribute by order for a month period (30 days) noting that I need to skip weekends (Friday and Saturday)

    Day 1 23.06.2014 Monday 20%
    Day 2 24.06.2014 Tuesday 10%
    Day 3 25.06.2014 Wednesday 7%
    Day 4 26.06.2014 Thursday 4%
    Day 5 27.06.2014 Friday
    Day 6 28.06.2014 Saturday
    Day 7 29.06.2014 Sunday 3%
    Day 8 30.06.2014 Monday 2%
    Day 9 01.07.2014 Tuesday 1%
    Day 10 02.07.2014 Wednesday 1%
    Day 11 03.07.2014 Thursday 2%
    Day 12 04.07.2014 Friday
    Day 13 05.07.2014 Saturday
    Day 14 06.07.2014 Sunday 6%
    Day 15 07.07.2014 Monday 8%
    Day 16 08.07.2014 Tuesday 3.0%
    Day 17 09.07.2014 Wednesday 1.0%
    Day 18 10.07.2014 Thursday 2.0%
    Day 19 11.07.2014 Friday
    Day 20 12.07.2014 Saturday
    Day 21 13.07.2014 Sunday 1.0%
    Day 22 14.07.2014 Monday 1.0%
    Day 23 15.07.2014 Tuesday 0.5%
    Day 24 16.07.2014 Wednesday 0.5%
    Day 25 17.07.2014 Thursday 0.5%
    Day 26 18.07.2014 Friday
    Day 27 19.07.2014 Saturday
    Day 28 20.07.2014 Sunday 0.5%
    Day 29 21.07.2014 Monday 0.5%
    Day 30 22.07.2014 Tuesday 0.5%

    Rates
    20%
    10%
    7%
    4%
    3%
    2%
    1%
    1%
    2%
    6%
    8%
    3.0%
    1.0%
    2.0%
    1.0%
    1.0%
    0.5%
    0.5%
    0.5%
    0.5%
    0.5%
    0.5%

    I need help to create this formula so that each time I change the date the rates are distributes accordingly

    Thank you
    Best Regards
    Lara
    Reply With QuoteRegister To Reply
  2. 06-24-2014, 07:45 AM #2
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,616

    Re: Create VBA to distribute rates and exclude weekend

    Why VBA?

    You can do this easily with formula. For instance if your rates are in M2:M23
    Write your start date in A2
    In A3 (and copy down) write
    Formula: copy to clipboard 
    Please Login or Register  to view this content.

    so once you write new start date all next dates change accordingly
    and in B2:
    Please Login or Register  to view this content.
    and copy down.

    PS. od course you can mimmic this formula in VBA but is it really needed?
    Attached Files Attached Files
    Best Regards,

    Kaper
    Reply With QuoteRegister To Reply
  3. 06-24-2014, 08:46 AM #3
    laramfarhat
    laramfarhat is offline
    Registered User
    Join Date
    06-24-2014
    Location
    uae
    MS-Off Ver
    2007
    Posts
    4

    Re: Create VBA to distribute rates and exclude weekend

    Hi Kaper
    Thank you a lot for your reply.
    You are right it's better with this simple formula
    But I have a small problem, when I copy the formula (even in your file) the formula turns into "#NAME?". I guess my excel 2007 might not be compatible with NETWORKDAYS.INTL formula??

    Thank you
    Best Regards
    Lara
    Reply With QuoteRegister To Reply
  4. 06-24-2014, 10:18 AM #4
    Kaper
    Kaper is offline
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often: Office 365 in Windows environment
    Posts
    8,616

    Re: Create VBA to distribute rates and exclude weekend

    You are probably right, this function probably has been introduced with ex2010. Then you can use standard networkdays (weekend sat-sun) and cheat a bit about dates (Fri+1 = Sat and Sat+1 = Sun isn't it
    so:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  5. 06-25-2014, 01:54 AM #5
    laramfarhat
    laramfarhat is offline
    Registered User
    Join Date
    06-24-2014
    Location
    uae
    MS-Off Ver
    2007
    Posts
    4

    Re: Create VBA to distribute rates and exclude weekend

    Hi Kaper
    Thank you a loooot It worked perfectly

    Best Regards
    Have a nive day
    Lara
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. how do i create macro that will auto color fill and lock columns if day is weekend HELP
    By nt_excel help in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-12-2013, 03:17 PM
  2. Trying to create a schedule of rates
    By suemcintyre in forum Excel General
    Replies: 5
    Last Post: 05-05-2013, 10:24 AM
  3. Calendar and weekend days, rates calculations? Very challenging
    By Sherburn Systems in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-20-2012, 07:04 AM
  4. [SOLVED] Weekend nightmare how to work out week day and weekend rates Help Please
    By Sherburn Systems in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-20-2012, 06:07 AM
  5. Calculating overtime on variable rates over the weekend
    By barrie.shardlow in forum Excel General
    Replies: 2
    Last Post: 11-03-2010, 05:41 PM

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1