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