?
Solved

simple change event for date range

Posted on 2012-08-14
4
Medium Priority
?
351 Views
Last Modified: 2012-08-15
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
0
Comment
Question by:JKCC
  • 2
  • 2
4 Comments
 
LVL 19

Expert Comment

by:Arno Koster
ID: 38292338
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
 

Author Comment

by:JKCC
ID: 38294059
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
 
LVL 19

Accepted Solution

by:
Arno Koster earned 2000 total points
ID: 38295475
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
 

Author Closing Comment

by:JKCC
ID: 38296743
Hi,

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

have a good day.

Cheers
JKCC
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question