Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    loop only work day (2000)

    I have my_var_date=31/03/2008 and my_var_date1=25/04/2008...
    How to make a loop from this two dates only for work day...
    similar:



    my_var_workday=31/03/2008
    my_var_workday=01/04/2008
    my_var_workday=02/04/2008
    my_var_workday=03/04/2008
    my_var_workday=04/04/2008
    my_var_workday=07/04/2008
    my_var_workday=08/04/2008
    ...
    my_var_workday=25/04/2008




    ....

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

    Re: loop only work day (2000)

    For my_var_workday = my_var_date To my_var_date1
    If Weekday(my_var_workday, 2) < 6 Then
    ' It's a work day; your code goes here
    End If
    Next my_var_workday

  3. #3
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loop only work day (2000)

    wath you think about:
    Sub DATA_RANGE_HANS()

    Dim my_var_date As Date
    Dim my_var_date1 As Date
    Dim my_var_workday As Date

    my_var_date = "31/03/2008"
    my_var_date1 = "25/04/2008"
    For my_var_workday = my_var_date To my_var_date1
    If Weekday(my_var_workday, 2) < 6 Then
    TEST = my_var_workday
    'here my macro call with the work day filled
    End If

    Next my_var_workday

    End Sub

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

    Re: loop only work day (2000)

    Have you tried it?

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loop only work day (2000)

    Yes work fine for me.
    but....
    But the code consider Christmas, Ester, NewYear...ecc automaticlly?
    And if ia want to exclude from work day National Day, Patronal Day...ecc and other date?

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

    Re: loop only work day (2000)

    No, this code doesn't take holidays into account.
    Enter the holidays in a column on the worksheet.
    Give the range with holidays a name, e.g. Holidays.
    Modify the code as follows:

    Sub DATA_RANGE_SALVATORE()
    Dim my_var_date As Date
    Dim my_var_date1 As Date
    Dim my_var_workday As Date
    my_var_date = "31/03/2008"
    my_var_date1 = "25/04/2008"
    For my_var_workday = my_var_date To my_var_date1
    If Weekday(my_var_workday, 2) < 6 Then
    If Range("Holidays").Find(What:=my_var_workday, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
    TEST = my_var_workday
    'here my macro call with the work day filled
    End If
    End If
    Next my_var_workday
    End Sub

  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loop only work day (2000)

    tks and "encantado" to named the macro with my name! "DATA_RANGE_SALVATORE"

    Work perfect

  8. #8
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: loop only work day (2000)

    Instead to use Name Range in sheet i can use:

    Set C = Sheets("GRUPPO").Columns("C").Find(what:=my_var_wo rkday, LookIn:=xlValues)
    If C Is Nothing Then
    ecc....eccc...
    end if

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

    Re: loop only work day (2000)

    Yes, that's possible.

Posting Permissions

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