Results 1 to 5 of 5
  1. #1
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402

    going loopy, I need some help with looping through dates

    Hi All, I know this is for an Excel worksheet but the code is writen in Access VBA for Excel Automation.



    I need some help with this. I am trying to insert this code into my excel automation vba. I have been asked to create a list of dates between 2 set dates, there will dbe dates allready within that range listed, I have to add the dates that are not listed EG

    If asked for dates between 17/2/2015 and 25/2/2015 AND the dates shown are allready listed ,then INSERT the missing dates ( 20/2/2015, 21/2/2015, 23/2/2015, 24/2/2015)

    17/2/2015
    18/2/2015
    19/2/2015
    22/2/2015
    25/2/2015

    So far i have been able loop through and add the missing dates but the ending of the loop is not working as expected, or maybe it is im not sure.
    some how i have to have a counter for the number of rows added and incorporate that into the loop somehow, thats were i need help.

    OR

    Am i overthinking this and is there an easier way?

    When its done i also have to add an extra date following on from the last date, this i have done, second last line of code.


    Code:
            last_row = Mid(last_cell, 4, 3)
            St_date = Me.start_date
            en_date = Me.End_date
            date_count = en_date - St_date
            Current_Worksheet.Range("A2").Select
            For i = 2 To date_count
    nxt_row:
                cur_cell = Current_Worksheet.Range("A" & i).Value
                nxt_cell = Current_Worksheet.Range("A" & i + 1).Value
                If nxt_cell = Me.End_date Then GoTo done_it
                If nxt_cell - 1 = cur_cell Then
                    i = i + 1
                    GoTo nxt_row
                Else
                    Current_Worksheet.Rows(i + 1 & ":" & i + 1).Insert Shift:=xlDown
                    Current_Worksheet.Range("A" & i + 1).Value = Current_Worksheet.Range("A" & i).Value + 1
                End If
            Next i
    done_it:
    
    
            last_cell = Current_Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address
            Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 1).Value = Current_Worksheet.Range("A" & Mid(last_cell, 4, 3)).Value + 1
            last_cell = Current_Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I tested your code with a blank worksheet. I specified date range 1/1/2015 - 1/31/2015. The code writes values 1 through 29 (not full date) into cells A3 through A31. What output do you want? Consider:
    Code:
    st_date = #1/15/2015#
    en_date = #1/20/2015#
    date_count = en_date - st_date
    Worksheets("Sheet1").Range("A2").Select
    For i = 2 To date_count + 2
        If IsEmpty(Worksheets("Sheet1").Range("A" & i)) Then
            Worksheets("Sheet1").Range("A" & i) = st_date
        ElseIf Worksheets("Sheet1").Range("A" & i) <> st_date Then
            Worksheets("Sheet1").Rows(i & ":" & i).Insert Shift:=xlDown
            Worksheets("Sheet1").Range("A" & i).Value = st_date
        End If
        st_date = st_date + 1
    Next
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    3,016
    Note that you’re using the British-style date format of day/month/year, whereas June7 is using the US date format of month/day/year! MS Access is US-centric and this can be problematic when dealing with dates. Allen Browne, one of your countrymen, has an excellent article on dealing with this, and I suspect that you’d benefit from reading it:

    International Dates in Access

    Linq ;0)>
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    trevor40's Avatar
    trevor40 is offline Advanced db Manager
    Windows XP Access 2003
    Join Date
    Feb 2014
    Location
    Australia
    Posts
    402
    Hi thanks for helping out, in the end i went with the code below, also i had to account for missing dates at the start and end of the selected range or no dates at all in the range.

    Code:
    st_date = Me.start_date
    en_date = Me.End_date
    Current_Worksheet.Range("A2").Select
    If IsEmpty(Current_Worksheet.Range("A2").Value) Then
    Current_Worksheet.Range("A2").Value = st_date
    Else
    End If
    try_again1:
    If Current_Worksheet.Range("A2").Value > st_date Then
    Current_Worksheet.Rows("2:2").Insert Shift:=xlDown
    Current_Worksheet.Range("A2").Value = Current_Worksheet.Range("A3").Value - 1
    GoTo try_again1
    Else
    End If
    For ib = 2 To 50
    nxt_row:
    cur_cell = Current_Worksheet.Range("A" & ib).Value
    nxt_cell = Current_Worksheet.Range("A" & ib + 1).Value
    If IsEmpty(nxt_cell) Then
    GoTo done_it
    Else
    End If
    If nxt_cell - 1 = cur_cell Then
    ib = ib + 1
    GoTo nxt_row
    Else
    Current_Worksheet.Rows(ib + 1 & ":" & ib + 1).Insert Shift:=xlDown
    Current_Worksheet.Range("A" & ib + 1).Value = Current_Worksheet.Range("A" & ib).Value + 1
    End If
    Next ib
    done_it:
    try_again2:
    last_cell = Current_Worksheet.Cells.SpecialCells(xlCellTypeLastCell).Address
    If Current_Worksheet.Range("A" & Mid(last_cell, 4, 3)).Value < en_date Then
    Current_Worksheet.Range("A" & Mid(last_cell, 4, 3) + 1).Value = Current_Worksheet.Range("A" & Mid(last_cell, 4, 3)).Value + 1
    GoTo try_again2
    Else
    End If

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Glad you have a solution. However, my simpler code did all that in tests.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Looping problem making me loopy.
    By stephenaa5 in forum Programming
    Replies: 1
    Last Post: 07-02-2013, 06:20 PM
  2. Looping
    By ddrew in forum Forms
    Replies: 8
    Last Post: 10-08-2012, 01:48 AM
  3. Looping Search
    By srmezick in forum Forms
    Replies: 5
    Last Post: 11-04-2011, 11:13 AM
  4. Looping through a tbl to find a certain value
    By cwf in forum Programming
    Replies: 1
    Last Post: 05-17-2010, 04:02 PM
  5. Looping syntax
    By fadiaccess in forum Access
    Replies: 1
    Last Post: 10-23-2009, 02:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums