View Full Version : Solved: Code not summing properly
Hoopsah
08-04-2009, 02:57 AM
Hi
I had help with this code some tme ago, but it seems to no longer sum correctly.
I input details for an absence, and it should work out the networkdays and update the absence workshhet tab in 3 diffeerent columns, once the macro under the "Submit Details" button is pressed.
1 column shows the total absence the other 2 are broken down by week and month - but the figures do not add up anymore (????)
I have attached a copy of the sheet with some data already input to show the difference in totals.
Any help greatly appreciated.
Bob Phillips
08-04-2009, 03:25 AM
Gerry,
Can you direct me where to look. There are lots of #VALUE errors, presumanly because of the liked workbook. If I add an entry on the form, where will the details go?
Maybe give an example of values and tell me where the wrong values are recorded.
Hoopsah
08-04-2009, 03:49 AM
Hi Bob,
thanks for having a look at this. I have amended the spreadsheet and broken the links so hopefully it will open for you now.
When I input the data on tab "Create new record" it should update tab "Absence Worksheet" column F - this works fine.
Column L should have a breakdown by month and column S should be a breakdown by week.
As you can see from the data entered, F has a sum of 94, L a sum of 51 and S a sum of 19.
These 3 columns should all be equal.
Cheers bob
Gerry
Bob Phillips
08-04-2009, 04:57 AM
Try replacing these two functions Gerry
Private Function CalculateMonthDays(ByVal StartDate As Date, _
ByVal EndDate As Date, _
ByVal FromDate As Date, _
ByVal ToDate As Date) As Long
If (StartDate <= FromDate And EndDate >= FromDate) Or _
(StartDate <= ToDate And EndDate >= ToDate) Or _
(EndDate >= FromDate And EndDate <= ToDate) Then
If FromDate > StartDate Then StartDate = FromDate
If ToDate < EndDate Then EndDate = ToDate
CalculateMonthDays = wsAbsence.Evaluate("NETWORKDAYS(" & CLng(StartDate) & "," & CLng(EndDate) & ")")
End If
End Function
Private Function CalculateWeekDays(ByVal StartDate As Date, _
ByVal EndDate As Date, _
ByVal FromDate As Date, _
ByVal ToDate As Date) As Long
If (StartDate <= FromDate And EndDate >= FromDate) Or _
(StartDate <= ToDate And EndDate >= ToDate) Or _
(EndDate >= FromDate And EndDate <= ToDate) Then
If FromDate > StartDate Then StartDate = FromDate
If ToDate < EndDate Then EndDate = ToDate
CalculateWeekDays = wsAbsence.Evaluate("NETWORKDAYS(" & CLng(StartDate) & "," & CLng(EndDate) & ")")
End If
End Function
Hoopsah
08-04-2009, 05:39 AM
Magic!!
I am looking at the changes you made and it makes perfect sense.
Works perfectly, thanks again for your help Bob
Cheers
Gerry
Bob Phillips
08-04-2009, 08:02 AM
I think the original tests didn't cater for all date spans, hopefully the new does.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.