simple change event for date range

hi experts,

on the data tab I have four columns of data being:

Name (cell B3 and down)
Type (cell C3 and down)
Start Date (cell D3 and down)
End Date (cell E3 and down)

Data is entered in a date range format or single day format.

I need the entered data on the data sheet to write the results to the output sheet everytime its updated so I presume a change event and break the date range down at a daily level. Please see spreadsheet attached for example.

thanks heaps guys
JKCCAsked:
Who is Participating?
 
Arno KosterConnect With a Mentor Commented:
when you want to add listings to the output sheet for every day in between start and end dates, use this code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim item_name As String
Dim item_type As String
Dim item_startdate As Date
Dim item_enddate As Date
Dim day As Date
Dim pos As Integer
        
    '-- first check if change is in appropriate cells
    If Not Intersect(Target, Range("B:E")) Is Nothing And Target.Row > 2 Then
        '-- check if name, type, start & end dates are present
        item_name = Range("B" & Target.Row)
        item_type = Range("C" & Target.Row)
        item_startdate = Range("D" & Target.Row)
        item_enddate = Range("E" & Target.Row)
        If item_name = "" Then Exit Sub
        If item_type = "" Then Exit Sub
        If item_startdate = CDate(0) Then Exit Sub
        If item_enddate = CDate(0) Then Exit Sub
        '-- write values to output sheet
        For day = item_startdate To item_enddate
            pos = Worksheets("output").UsedRange.Row + Worksheets("output").UsedRange.Rows.Count
            Worksheets("output").Range("A" & pos) = item_name
            Worksheets("output").Range("B" & pos) = item_type
            Worksheets("output").Range("C" & pos) = day
            Worksheets("output").Range("D" & pos) = day
        Next day
    End If

End Sub

Open in new window

0
 
Arno KosterCommented:
JKCC, you forgot to attach a spreadsheet.

Anyways it should be something like

Private Sub Worksheet_Change(ByVal Target As Range)
Dim item_name As String
Dim item_type As String
Dim item_startdate As Date
Dim item_enddate As Date
Dim pos As Integer
        
    '-- first check if change is in appropriate cells
    If Not Intersect(Target, Range("B:E")) Is Nothing And Target.Row > 2 Then
        '-- check if name, type, start & end dates are present
        item_name = Range("B" & Target.Row)
        item_type = Range("C" & Target.Row)
        item_startdate = Range("D" & Target.Row)
        item_enddate = Range("E" & Target.Row)
        If item_name = "" Then Exit Sub
        If item_type = "" Then Exit Sub
        If item_startdate = CDate(0) Then Exit Sub
        If item_enddate = CDate(0) Then Exit Sub
        '-- write values to output sheet
        pos = Worksheets("output").UsedRange.Row + Worksheets("output").UsedRange.Rows.Count
        Worksheets("output").Range("A" & pos) = item_name
        Worksheets("output").Range("B" & pos) = item_enddate - item_startdate
    End If

End Sub

Open in new window

0
 
JKCCAuthor Commented:
Thankyou...

i'll attach it now. it almost works the way I want. see attached.

the values don't start below the next line item in the output sheet and it doesn't write
all the dates. (one line item after the other) in the consecutive format.

I can see where the values are directed, but not in the right cells. I don't want the dates minused (just all the in between dates listed for that entry)...

hope this makes sense!

sorry and thankyou.
Book1.xlsx
0
 
JKCCAuthor Commented:
Hi,

Yup, that works! Thanks heaps for time and effort.

have a good day.

Cheers
JKCC
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.