Hi,
I have a user form with 2 tabs to input date data (not too many problems there) and also retrieve the sum of days for different tasks(problems there). I have five headings in row one, Columns A:B "Offshore", E:F "Travelling To Work", I:J "Travelling From Work", M:N "Workshop" & Q:S "Courses".
In row two I have date sets; "Start Date" and "End Date" under each heading. The next colum after each date set is free for totals.
Travel To (Start Date) is the only column that will have data in every row and I have referenced the other columns to this on the input side of the userform, therefore if someone goes Offshore the Offshore dates will align with the Travel To dates. That employee may next go to the workshop and that will leave a blank row between the last and next Offshore dates.
What I am attempting unsucessfully to do is check that the row date pairs ("Travel To" and "Travel From"), or part thereof, are in the current calendar year, sum these to the total column then sum the total of the column, I will then display then in a list box. This will to be done for each pair of columns so when a user selects the employee they will be able to see for the calendar year the days offshore, travel days, workshop days and course days. Another thing is that there could be a "Travel To" date but not a "Travel From" date as the employee could still be on the job.
Below is some downloaded code (thanks) that I have been attempting to massage into a usable form without success, to be honest I am unsure if I'm barking up the wrong tree with this code and at the moment can't step through it for errors, all my own. I have searched the forum but can't nut this one out without a bit of advice and guidance.
Cheers Dave
Excel 2010 on Windows 7
I have a user form with 2 tabs to input date data (not too many problems there) and also retrieve the sum of days for different tasks(problems there). I have five headings in row one, Columns A:B "Offshore", E:F "Travelling To Work", I:J "Travelling From Work", M:N "Workshop" & Q:S "Courses".
In row two I have date sets; "Start Date" and "End Date" under each heading. The next colum after each date set is free for totals.
Travel To (Start Date) is the only column that will have data in every row and I have referenced the other columns to this on the input side of the userform, therefore if someone goes Offshore the Offshore dates will align with the Travel To dates. That employee may next go to the workshop and that will leave a blank row between the last and next Offshore dates.
What I am attempting unsucessfully to do is check that the row date pairs ("Travel To" and "Travel From"), or part thereof, are in the current calendar year, sum these to the total column then sum the total of the column, I will then display then in a list box. This will to be done for each pair of columns so when a user selects the employee they will be able to see for the calendar year the days offshore, travel days, workshop days and course days. Another thing is that there could be a "Travel To" date but not a "Travel From" date as the employee could still be on the job.
Below is some downloaded code (thanks) that I have been attempting to massage into a usable form without success, to be honest I am unsure if I'm barking up the wrong tree with this code and at the moment can't step through it for errors, all my own. I have searched the forum but can't nut this one out without a bit of advice and guidance.
Cheers Dave
Excel 2010 on Windows 7
Code:
Private Sub daysCount()
Dim rngDates As Range
Dim dtEndDate As Date
Dim stStartDate As Date
Dim rngCell As Range
Dim sngResult As Single
Dim FinalRow As Long, i As Integer
FinalRow = Range("E65536").End(xlUp).Row
'set the range containing dates
Set rngDates = Range(Array("A4:FinalRow"))
'set start date
stStartDate = DateSerial(Year(Now), 1, 1)
'set end date
dtEndDate = DateSerial(Year(Now), 12, 31)
'loop through each cell in the range
For Each rngCell In rngDates
If rngCell.Offset(, 1).IsEmpty.Ignore Then GoTo Line44
Next
If rngCell.Value >= stStartDate & rngCell.Value <= dtEndDate Then
Cell.Offset(, 1).AsRange
End If
Line44:
End Sub
[\Code]
