1 Attachment(s)
Second Code for Nelson using Admin Formula Idea
Hi Nelson,
This is another solution separate to mine form posts 5 6 and 7
Here is a new Full code: Sub IJAdjustKAddTotalAllWorksheet()
Post #20
http://www.excelfox.com/forum/showth...0072#post10072
The check criteria is now the background color, ( as alternative to Friday as in the first code ) '3d) We need to check Interior color . I got it from this quick code
Code:
' Rem Ref '_- http://www.excelfox.com/forum/showthread.php/2145-Excel-VBA-Interception-and-Implicit-Intersection-and-VLookUp?p=10062#post10062
'_- http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells?p=10012#post10012
Sub GetInteriorColor() ' https://msdn.microsoft.com/en-us/library/office/ff840499.aspx http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10070#post10070
Dim ObjColor As Object
Set ObjColor = Range("E3").Interior: Debug.Print ObjColor.Color ' Looks Yellow is Double 65535 in Immedite window ( Ctrl+g )
Set ObjColor = Range("E4").Interior: Debug.Print ObjColor.Color ' Looks like no color is Double 16777215
Dim RngDatesColors As Range ' Select RngDatesColors and hit Shift+F9 to get Range object in watch window. navigate by scrolling and opening with + signs the .Interor Object
Set RngDatesColors = ThisWorkbook.Worksheets("121").Range("A1").Offset(0, 4).Resize(31, 1) ' .Interior shows only one value for the entire range which seems to be that for a cell with no background color as seen in the screenshot above all the cells have a colour
End Sub
which, for the BEFORE worksheet “121” tells me that the yellow is an interior taken as a Double of 65535
InteriorColor.JPG http://imgur.com/MavnwLi
Attachment 1870
I make an extra array, arrK() to take the string values for the helper column K, to hold “H” for Holiday hrs or “N” for normal hrs
I no longer take in a dates array to check for Friday. Instead, I take in an Array, arrDteClr(), of the dates interior color. This is a bit inefficient as there is no Interior colors array in the Excel Range object. ( .Interior property for a Range object shows only one value for the entire range which seems to be that as for no color as seen in the screenshot above unless all the cells have a colour, in which case it gives the color for that )
So I have to take in each value in a new loop, 'InnerLoop for dates background colors ( so I could just as well have checked the worksheet, but I made an array for consistency as I am predominantly using arrays in my code).
Code section '3e) from the first code is not now used to calculate totals. ( and neither are the variables NOHrsV2 and HOHrsV2 which were used for those totals
Code section ‘3f) is modified to paste out the formulas from Admin instead of the totals values
In section ‘3g) there is a line which pastes out that help column
So in next post is just the BEFORE ( “File1 Before.xlsx” ) data range for the first worksheet “121”.
(as in Post #6
http://www.excelfox.com/forum/showth...0068#post10068 )
Then in the final 2 couple of post is the Output ( AFTER ) for the new code for one of the Worksheets ( “121” ). The code works on the same Input ( BEFORE ) as in Post #6
http://www.excelfox.com/forum/showth...0068#post10068
Further to the TWO codes you now have from US .......... :)
Hi Nelson,
Quote:
Originally Posted by
nelsondavid
..i need normal OT and Holiday OT to appear exactly in cell G34 and J34 ...
I think you can see what part of the code determines where the output goes
Code:
'3f) Paste out final Totals and days to current Worksheet
Let wsStear.Range("G34").Value = NOHrsV2 * 24 'Normal Overtime is held in Array as fraction of a day
Let wsStear.Range("J34").Value = HOHrsV2 * 24 'Holiday Overtime is held in Array as fraction of a day
Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
'3g) Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are changed
Code:
'3f) Paste out final Totals formulas and days to current Worksheet
Let wsStear.Range("G35").Value = "=SUMIF(K1:K" & lr & ",""N"",J1:J" & lr & ")*24"
Let wsStear.Range("J35").Value = "=SUMIF(K1:K" & lr & ",""H"",J1:J" & lr & ")*24"
Let wsStear.Range("C34").Value = TDays ' The constant value of Total days is simply added to cell C34
'3g) Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are changed ' And can paste out help column if you like
I am sure you can understand how to modify to change which cells are used for where the total appear
_............................................
Quote:
Originally Posted by
nelsondavid
.... Can i use the same code for every month ...
Yes..
Quote:
Originally Posted by
nelsondavid
. if yes what changes i need to do from my end..
..The code is not in any way dependant on any month information......There is no reason that I can see to need to change anything..
Quote:
Originally Posted by
nelsondavid
....what changes i need to do from my end..
You should not need to change your end, leave it alone, at least as far as any effect on the Months are concerned
_................................................. .......
Quote:
Originally Posted by
nelsondavid
...I have around 800 sheets ...will the code work on all sheets in one go...
The code is designed to work on multiple worksheets.
Obviously I only tested it with two.
If I were you, I would test it first with a larger amount, 10, 30, 60, .100 .......etc.. then finally 800 etc.
_................................................. ...
In my opinion, it is very important that you try to understand the code...
Alan.
P.s.
Quote:
Originally Posted by
nelsondavid
..The code seems to be working fine..
We have given you 2 codes
From Alan
http://www.excelfox.com/forum/showth...age2#post10066
From Alan and Admin
http://www.excelfox.com/forum/showth...age2#post10072
I think we may almost know what is to be done !
These are the some of the criteria my next code will work on: ( These are those criteria which were unclear until now ! )
Holidays are shown in yellow background color
Normal working days have no background color
The data rows to be considered are those filled with dates in column E
“TOTAL NO. OF DAYS” ( to be placed by the code in cell C34 )
For all Months, the “TOTAL NO. OF DAYS” ( to be placed in cell C34 ) is not necessarily the number of days worked.
The formula for calculating this is:
_Assuming the employee is not Absent for any day, then the “TOTAL NO. OF DAYS” is always taken as 30
_ If the employee has one or more normal days of absence, ( normal days with no total working hours ), then the formula for calculating “TOTAL NO. OF DAYS” is as follows:
TOTAL NO. OF DAYS = 30 – ( Count of “ABSENT” )
“ABSENT” ( to be written in some rows in Column K by the program )
Count of “ABSENT” is the number of occurrences of ABSENT in column K in the final ( After ) Worksheet “ABSENT” is to be written in some rows of column K by the code under certain criteria.
( “ABSENT” is not necessarily the normal working days in which an employee is absent and / or has no total working hours. )
“ABSENT” is to be written in column K by the code under the following criteria:
_ For the rows of all normal days when the employee has no working hours, ( days when the employee is absent ), “ABSENT” is to be written in column K.
_ In addition , should it occur that an employee is absent for both the days before and after a holiday, then for the ( Holiday ) row in between those two days, “ABSENT” is to be written in column K.
( No consideration of this ““ABSENT” criteria thereof” is made for the case of a Holiday at the first or last “Entries” )
( The number of “Entries” is taken as the filled dates in column E )
Column L ( help column )
Nelson has chosen the second code. It puts formulas in cells C34, G34, and J34.
This requires “H” or “N” to indicate Holiday or Normal working day. This will be written by the code in column L
_......................
Nelson:
Please examine the above criteria , and tell me if this is correct ?
Alan
:)
