Appendix Thread. ( Codes for other Threads, etc.) Event Coding Drpdown Data validation

'Testies: NOT FINAL CODE --- TEST CODE FOR LATER REFERRENCE ! TEST ! --- Testies to you '_-
' Code for approximately Posts: 14 - 23
Sub IJAdjustLAddTotalAllWorksheetCode3()

For Thread ' http://www.excelfox.com/forum/showth...0078#post10078

Code:

'Testies: NOT FINAL CODE --- TEST CODE FOR LATER REFERRENCE ! TEST ! --- Testies to you '_- ' Code for approximately Posts: 14 - 23 Sub IJAdjustLAddTotalAllWorksheetCode3() 'http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10078#post10078 Rem 1) Workbooks Info. Dim Wb As Workbook ' Dim: For Object variabls: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helped explain what occurs when passing an Object to a Call ed Fucntion or Sub Routine By Val ue. In such an occurance, VBA actually passes a copy of the pointer. So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence. But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.) Set Wb = ActiveWorkbook ' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance Dim wsStear As Worksheet ' Used for each Worksheet counting Tabs from left from 1 To Total Rem 2) varables for some totals ;) 'Const TDays As Long = 30 'Total days just taken as 30 ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. ) Dim Dte As Date, DteNo As Long ' I am hoping Dte will sort out getting a date in a format that I can use the Weekday function to see what week day it is and get that as a nuumber to check for.. Rem 3) Loop through worksheets and give some Totals Dim Cnt As Long ' Loop Bound variable count for going through all worksheets '3a) main Loop start===================================================== For Cnt = 1 To Wb.Worksheets.Count ' The Worksheets collection Object Property returns the number of worksheet items in the Workbook Set wsStear = Wb.Worksheets.Item(Cnt) ' At each loop the variable is set to the current Worksheet counting from the Cnt'ths tab from left Dim lr As Long ' Used for last row number in column E Let lr = wsStear.Range("E" & Rows.Count & "").End(xlUp).Row ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Rows Property) has the Property .End ( argument "Looking back up" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Row number of that cell: Rows.Count is the very last row number in your Worksheet. It is different for earlier versions of Excel. The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up. Let lr = 30 ' maybe nelson means thís ? "...For all Month no. of days we take as 30 only..." Dim FstDtaCel As Range: Set FstDtaCel = wsStear.Range("A1") ' Worksheets Range(" ") Property used to return Range object of first cell in second row '3b) Data arrays from worksheet. We need columns E H I J .... Date ( Column E ) and Total hrs ( Column H ) are required to use in calculations Dim arrInNorm() As Variant, arrInOver() As Variant ' In the next lines the .Value2 or .value Property is applied a Range object which presents the Value or Value2 value or values in a single variable of appropriate type or a field of member Elements of varaint types.We are expecting the latter, so declare ( Dim ) a dynamic Array variable appropriately. It must be dynamic as its size will be defined at that assignment Let arrInNorm() = FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 ' I ' Normal Hrs ( Column I ) are needed as they must be set to zero for Holy ?? Holidays ?? Friday ?? Let arrInOver() = FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 ' J ' Overtime ( Column J ) is needed as it will be changed and then used in calculations Dim arrTotHrs() As Variant ' ,' ## ' arrDteClr() As Variant Let arrTotHrs() = FstDtaCel.Offset(0, 7).Resize(lr, 1).Value ' H ' ' One thing you pick up when learning VBA programming is that referring to cells from one to another via an offset is both fundamental and efficient. That makes sense as Excel is all about using the offsets mentioned above. So like if you use them you can “cut out the middle man”. ( The middle man here might be considered as, for example, in VBA, using extra variables for different Range objects: A fundamental thing to do with any cell ( or strictly speaking the Range object associated to a cell ) is the Range Item Property of any range Object, through which you can “get at” any other Range object. http://www.excelforum.com/showthread.php?t=1154829&page=13&p=4563838&highlight=#post4563838 ( It is often quicker than using a separate variable for each Range object – probably as all the variable does is hold the offset , so you might as well use the offset in the first place.. ) ' Similarly Another thing you pick up along the way is that the cells ( or strictly speaking the Range objects associated with it ) can be organised into groups of cells which then are also called Range objects and are organised in their constituent parts exactly the same as for the single cell Range object. Once again this is all an indication of organising so that we get at information by sliding along a specific amount ( offset value). The Offset and Resize properties therefore return a new range object. I use the .Value 2 here as i seemed to get it for .Value anyway, not sure why yet, - so i thought be on the safe side , get it always and work somehow with that for now and convert as necerssary. Also 1 breadth Arrays due to Alan Intercept theory are held in such a ways as to be very effient in usage of values within Dim arrL() As String 'I know the size, but must make it dynamic as Dim declaration only takes numbers, and so I use ReDim method below wehich can also take variables or formulas ReDim arrL(1 To UBound(arrInNorm(), 1), 1 To 1) ' Any array first dimension ("row") will do Dim arrAbscentK() As String 'K column to have ABSCENT in for person Abscent on not Holiday ReDim arrAbscentK(1 To UBound(arrInNorm(), 1), 1 To 1) 'Must Loop to get interior color as this will not work. ' ## ' Let arrDteClr() = FstDtaCel.Offset(0, 4).Resize(lr, 1).Interior.Color ' because .Interior property for a Range object shows only one value for the entire range which seems to be zero unless all the cells have a colour Dim arrDteClr() As Double, rngDts As Range Set rngDts = FstDtaCel.Offset(0, 4).Resize(lr, 1) Dim Rws As Long: ReDim arrDteClr(1 To lr, 1 To 1) ' so must loop in each Interior color value For Rws = 1 To UBound(arrDteClr(), 1) Step 1 'InnerLoop for dates background colors Let arrDteClr(Rws, 1) = rngDts.Item(Rws, "A").Interior.Color Next Rws '3c) Inner loop for rows Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping Dim ValidHoliday As Boolean: Let ValidHoliday = True 'Assume for now Holiday days are valid for Holiday adjustments For ShtCnt = 1 To UBound(arrDteClr(), 1) Step 1 '------------------- For "rows" in data arrays '3d) We need to check Interior color, and a few other things, Adjust columns I and J so that column I has no hours for holiday day and total hours goes to over time hours with criteria 9 or less than 9 hrs all total hours added overtime, 10 or above 10 hrs one hour deducted from total hours and added to column J ..... and add a H or N in helper column K If arrDteClr(ShtCnt, 1) = 65535 Then ' We have a Holiday, ...but... have some other checks If Not (ShtCnt = 1 Or ShtCnt = UBound(arrDteClr(), 1)) Then ' ....but... Possible futher checks for not adjusting Normal Total Hrs to overtime and remove normal Hrs 'It is possible to check for absent before and after current day If (arrTotHrs(ShtCnt - 1, 1) <> Empty And arrTotHrs(ShtCnt + 1, 1)) <> Empty Then '...."...holiday is deducted if the person does not come the day before and after the holiday...".... Let ValidHoliday = False Else Let ValidHoliday = True End If Else 'It is not possible for absence before AND after to check for absence as one will lie in last or next month End If ' We remmain at default or last set true or just set true 'We had Holiday ... If ValidHoliday = True Then ' ...and all conditions for valid Holiday pay adjustments 'Conditions met to adjust make all of 1 less of Normal Hrs to overtime If (arrTotHrs(ShtCnt, 1) * 24) <= 9 Then '(i) If Total Hrs are less than or equal to 9 ,Then all Total Hrs are added to Overtime Hrs Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) ' Given To ' Added to arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1) ElseIf (arrTotHrs(ShtCnt, 1) * 24) > 9 Then ' (ii) If Total Hrs are less greater than 9 , Then ( Total Hrs - 1 ) are added to Overtime Hrs Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) - 1 / 24 ' Given To ' arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1) - 1 / 24 'Added to 1 hr less overtime for more than 9 hrs worked End If Let arrInNorm(ShtCnt, 1) = Empty ' (iii) As array is variant type can empty Remove normal Hrs Array for(Column I) is then set tom zerow for this "row"o Let arrL(ShtCnt, 1) = "H" ' ' (iv)H Give string, "" value of H for Holiday in Admin's help column Else ' We had a Holiday but abscence before and after, we need Let ValidHoliday = True 'we need to reset to true End If Else ' No Holy Holiday Let arrL(ShtCnt, 1) = "N" ' give string N for normal ' (iv)N End If If arrTotHrs(ShtCnt, 1) = Empty And Not arrDteClr(ShtCnt, 1) = 65535 Then Let arrAbscentK(ShtCnt, 1) = "ABSENT" ' column K absent days should be marked as ABSENT. '3e) ' from last code, is not now used to calculate totals Next ShtCnt '--------------------------End Inner loop for rows----- '3f) Paste out final Totals and days to current Worksheet Let wsStear.Range("G34").Value = "=SUMIF(L1:L" & lr & ",""N"",J1:J" & lr & ")*24" Let wsStear.Range("J34").Value = "=SUMIF(L1:L" & lr & ",""H"",J1:J" & lr & ")*24" Let wsStear.Range("C34").Value = "=COUNT(F1:F31)" ' 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 Let FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 = arrInOver() ' J ' The required spreadsheet cells range has its Range Object .Value2 values filled an allowed direct assignment to an array of values Let FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 = arrInNorm() ' I Let FstDtaCel.Offset(0, 11).Resize(lr, 1).Value2 = arrL() ' L Let FstDtaCel.Offset(0, 10).Resize(lr, 1).Value2 = arrAbscentK() ' K '3h) Set Booleans for Next Cnt '==End main Loop============================================== End Sub

Post 22 Before ( HTML )

Post 22 Before
http://www.excelfox.com/forum/showth...0090#post10090

<b>Excel 2007 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">21.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">22.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">23.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">15:00</td><td style="text-align: right;;">8:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">0:00</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">24.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">25.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">26.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">27.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">17:00</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">28.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">29.Dec.16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">30.Dec.16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">31.Dec.16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">1.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">2.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">3.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">4.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">5.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">6.Jan.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">7.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">8.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">9.Jan.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">10.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">11.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">12.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">13.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">14.Jan.17</td><td style="text-align: right;;">7:30</td><td style="text-align: right;;">17:30</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">15.Jan.17</td><td style="text-align: right;;">7:30</td><td style="text-align: right;;">17:30</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">16.Jan.17</td><td style="text-align: right;;">7:30</td><td style="text-align: right;;">17:30</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">17.Jan.17</td><td style="text-align: right;;">7:30</td><td style="text-align: right;;">17:30</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">18.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">19.Jan.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;">121</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">20.Jan.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="font-weight: bold;background-color: #FFFFFF;;"></td><td style="font-weight: bold;;"></td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: right;;">Normal Overtime  -----></td><td style="font-weight: bold;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">Holiday Overtime  -----></td><td style="font-weight: bold;;"></td></tr></tbody></table><p style="width:1.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">121</p><br /><br />

Post 22 Before ( BB Code )

Post 22 Before ( BB Code )

http://www.excelfox.com/forum/showth...0090#post10090

Using Excel 2007 32 bit

_Row\^Col
C

D

E

F

G

H

I

J

1

121
TEAM LEADER
21.Dec.16

7:00

18:00

11:00

9:00

2:00

2

121
TEAM LEADER
22.Dec.16

7:00

18:00

11:00

9:00

2:00

3

121
TEAM LEADER
23.Dec.16

7:00

15:00

8:00

9:00

0:00

4

121
TEAM LEADER
24.Dec.16

7:00

18:00

11:00

9:00

2:00

5

121
TEAM LEADER
25.Dec.16

7:00

18:00

11:00

9:00

2:00

6

121
TEAM LEADER
26.Dec.16

7:00

18:00

11:00

9:00

2:00

7

121
TEAM LEADER
27.Dec.16

7:00

17:00

10:00

9:00

1:00

8

121
TEAM LEADER
28.Dec.16

7:00

18:00

11:00

9:00

2:00

9

29.Dec.16

10

30.Dec.16

11

31.Dec.16

12

121
TEAM LEADER
1.Jan.17

7:00

18:00

11:00

9:00

2:00

13

121
TEAM LEADER
2.Jan.17

7:00

18:00

11:00

9:00

2:00

14

121
TEAM LEADER
3.Jan.17

7:00

18:00

11:00

9:00

2:00

15

121
TEAM LEADER
4.Jan.17

7:00

18:00

11:00

9:00

2:00

16

121
TEAM LEADER
5.Jan.17

7:00

18:00

11:00

9:00

2:00

17

121
TEAM LEADER
6.Jan.17

18

121
TEAM LEADER
7.Jan.17

7:00

18:00

11:00

9:00

2:00

19

121
TEAM LEADER
8.Jan.17

7:00

18:00

11:00

9:00

2:00

20

121
TEAM LEADER
9.Jan.17

21

121
TEAM LEADER
10.Jan.17

7:00

18:00

11:00

9:00

2:00

22

121
TEAM LEADER
11.Jan.17

7:00

18:00

11:00

9:00

2:00

23

121
TEAM LEADER
12.Jan.17

7:00

18:00

11:00

9:00

2:00

24

121
TEAM LEADER
13.Jan.17

7:00

18:00

11:00

9:00

2:00

25

121
TEAM LEADER
14.Jan.17

7:30

17:30

10:00

9:00

1:00

26

121
TEAM LEADER
15.Jan.17

7:30

17:30

10:00

9:00

1:00

27

121
TEAM LEADER
16.Jan.17

7:30

17:30

10:00

9:00

1:00

28

121
TEAM LEADER
17.Jan.17

7:30

17:30

10:00

9:00

1:00

29

121
TEAM LEADER
18.Jan.17

7:00

18:00

11:00

9:00

2:00

30

19.Jan.17

31

121
TEAM LEADER
20.Jan.17

32

33

34

Normal Overtime ----->

Holiday Overtime ----->

Worksheet: Post22Before121

_Row\^Col	C	D	E	F	G	H	I	J
1	121	TEAM LEADER	21.Dec.16	7:00	18:00	11:00	9:00	2:00
2	121	TEAM LEADER	22.Dec.16	7:00	18:00	11:00	9:00	2:00
3	121	TEAM LEADER	23.Dec.16	7:00	15:00	8:00	9:00	0:00
4	121	TEAM LEADER	24.Dec.16	7:00	18:00	11:00	9:00	2:00
5	121	TEAM LEADER	25.Dec.16	7:00	18:00	11:00	9:00	2:00
6	121	TEAM LEADER	26.Dec.16	7:00	18:00	11:00	9:00	2:00
7	121	TEAM LEADER	27.Dec.16	7:00	17:00	10:00	9:00	1:00
8	121	TEAM LEADER	28.Dec.16	7:00	18:00	11:00	9:00	2:00
9			29.Dec.16
10			30.Dec.16
11			31.Dec.16
12	121	TEAM LEADER	1.Jan.17	7:00	18:00	11:00	9:00	2:00
13	121	TEAM LEADER	2.Jan.17	7:00	18:00	11:00	9:00	2:00
14	121	TEAM LEADER	3.Jan.17	7:00	18:00	11:00	9:00	2:00
15	121	TEAM LEADER	4.Jan.17	7:00	18:00	11:00	9:00	2:00
16	121	TEAM LEADER	5.Jan.17	7:00	18:00	11:00	9:00	2:00
17	121	TEAM LEADER	6.Jan.17
18	121	TEAM LEADER	7.Jan.17	7:00	18:00	11:00	9:00	2:00
19	121	TEAM LEADER	8.Jan.17	7:00	18:00	11:00	9:00	2:00
20	121	TEAM LEADER	9.Jan.17
21	121	TEAM LEADER	10.Jan.17	7:00	18:00	11:00	9:00	2:00
22	121	TEAM LEADER	11.Jan.17	7:00	18:00	11:00	9:00	2:00
23	121	TEAM LEADER	12.Jan.17	7:00	18:00	11:00	9:00	2:00
24	121	TEAM LEADER	13.Jan.17	7:00	18:00	11:00	9:00	2:00
25	121	TEAM LEADER	14.Jan.17	7:30	17:30	10:00	9:00	1:00
26	121	TEAM LEADER	15.Jan.17	7:30	17:30	10:00	9:00	1:00
27	121	TEAM LEADER	16.Jan.17	7:30	17:30	10:00	9:00	1:00
28	121	TEAM LEADER	17.Jan.17	7:30	17:30	10:00	9:00	1:00
29	121	TEAM LEADER	18.Jan.17	7:00	18:00	11:00	9:00	2:00
30			19.Jan.17
31	121	TEAM LEADER	20.Jan.17
32
33
34				Normal Overtime ----->			Holiday Overtime ----->

After from last two posts after running code :
Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4()

Using Excel 2007 32 bit

_Row\^Col
C

D

E

F

G

H

I

J

K

L

1

121
TEAM LEADER
21.Dec.16

7:00

18:00

11:00

9:00

2:00
N

2

121
TEAM LEADER
22.Dec.16

7:00

18:00

11:00

9:00

2:00
N

3

121
TEAM LEADER
23.Dec.16

7:00

15:00

8:00

8:00
H

4

121
TEAM LEADER
24.Dec.16

7:00

18:00

11:00

9:00

2:00
N

5

121
TEAM LEADER
25.Dec.16

7:00

18:00

11:00

9:00

2:00
N

6

121
TEAM LEADER
26.Dec.16

7:00

18:00

11:00

9:00

2:00
N

7

121
TEAM LEADER
27.Dec.16

7:00

17:00

10:00

9:00

1:00
N

8

121
TEAM LEADER
28.Dec.16

7:00

18:00

11:00

9:00

2:00
N

9

29.Dec.16
ABSENT N

10

30.Dec.16
ABSENT

11

31.Dec.16
ABSENT N

12

121
TEAM LEADER
1.Jan.17

7:00

18:00

11:00

9:00

2:00
N

13

121
TEAM LEADER
2.Jan.17

7:00

18:00

11:00

9:00

2:00
N

14

121
TEAM LEADER
3.Jan.17

7:00

18:00

11:00

9:00

2:00
N

15

121
TEAM LEADER
4.Jan.17

7:00

18:00

11:00

9:00

2:00
N

16

121
TEAM LEADER
5.Jan.17

7:00

18:00

11:00

9:00

2:00
N

17

121
TEAM LEADER
6.Jan.17
H

18

121
TEAM LEADER
7.Jan.17

7:00

18:00

11:00

9:00

2:00
N

19

121
TEAM LEADER
8.Jan.17

7:00

18:00

11:00

9:00

2:00
N

20

121
TEAM LEADER
9.Jan.17
ABSENT N

21

121
TEAM LEADER
10.Jan.17

7:00

18:00

11:00

9:00

2:00
N

22

121
TEAM LEADER
11.Jan.17

7:00

18:00

11:00

9:00

2:00
N

23

121
TEAM LEADER
12.Jan.17

7:00

18:00

11:00

9:00

2:00
N

24

121
TEAM LEADER
13.Jan.17

7:00

18:00

11:00

10:00
H

25

121
TEAM LEADER
14.Jan.17

7:30

17:30

10:00

9:00

1:00
N

26

121
TEAM LEADER
15.Jan.17

7:30

17:30

10:00

9:00

1:00
N

27

121
TEAM LEADER
16.Jan.17

7:30

17:30

10:00

9:00

1:00
N

28

121
TEAM LEADER
17.Jan.17

7:30

17:30

10:00

9:00

1:00
N

29

121
TEAM LEADER
18.Jan.17

7:00

18:00

11:00

9:00

2:00
N

30

19.Jan.17
ABSENT N

31

121
TEAM LEADER
20.Jan.17
H

32

33

34
25
Normal Overtime ----->
39
Holiday Overtime ----->
18

Worksheet: Aftert121

Using Excel 2007 32 bit

_Row\^Col
B

C

D

E

F

G

H

I

J

34
TOTAL NO. OF DAYS -----> =30-COUNTIF(K1:K31,"ABSENT")
Normal Overtime ----->
=SUMIF(L1:L31,"N",J1:J31)*24
Holiday Overtime ----->
=SUMIF(L1:L31,"H",J1:J31)*24

Worksheet: After121

_Row\^Col	C	D	E	F	G	H	I	J	K	L
1	121	TEAM LEADER	21.Dec.16	7:00	18:00	11:00	9:00	2:00		N
2	121	TEAM LEADER	22.Dec.16	7:00	18:00	11:00	9:00	2:00		N
3	121	TEAM LEADER	23.Dec.16	7:00	15:00	8:00		8:00		H
4	121	TEAM LEADER	24.Dec.16	7:00	18:00	11:00	9:00	2:00		N
5	121	TEAM LEADER	25.Dec.16	7:00	18:00	11:00	9:00	2:00		N
6	121	TEAM LEADER	26.Dec.16	7:00	18:00	11:00	9:00	2:00		N
7	121	TEAM LEADER	27.Dec.16	7:00	17:00	10:00	9:00	1:00		N
8	121	TEAM LEADER	28.Dec.16	7:00	18:00	11:00	9:00	2:00		N
9			29.Dec.16						ABSENT	N
10			30.Dec.16						ABSENT
11			31.Dec.16						ABSENT	N
12	121	TEAM LEADER	1.Jan.17	7:00	18:00	11:00	9:00	2:00		N
13	121	TEAM LEADER	2.Jan.17	7:00	18:00	11:00	9:00	2:00		N
14	121	TEAM LEADER	3.Jan.17	7:00	18:00	11:00	9:00	2:00		N
15	121	TEAM LEADER	4.Jan.17	7:00	18:00	11:00	9:00	2:00		N
16	121	TEAM LEADER	5.Jan.17	7:00	18:00	11:00	9:00	2:00		N
17	121	TEAM LEADER	6.Jan.17							H
18	121	TEAM LEADER	7.Jan.17	7:00	18:00	11:00	9:00	2:00		N
19	121	TEAM LEADER	8.Jan.17	7:00	18:00	11:00	9:00	2:00		N
20	121	TEAM LEADER	9.Jan.17						ABSENT	N
21	121	TEAM LEADER	10.Jan.17	7:00	18:00	11:00	9:00	2:00		N
22	121	TEAM LEADER	11.Jan.17	7:00	18:00	11:00	9:00	2:00		N
23	121	TEAM LEADER	12.Jan.17	7:00	18:00	11:00	9:00	2:00		N
24	121	TEAM LEADER	13.Jan.17	7:00	18:00	11:00		10:00		H
25	121	TEAM LEADER	14.Jan.17	7:30	17:30	10:00	9:00	1:00		N
26	121	TEAM LEADER	15.Jan.17	7:30	17:30	10:00	9:00	1:00		N
27	121	TEAM LEADER	16.Jan.17	7:30	17:30	10:00	9:00	1:00		N
28	121	TEAM LEADER	17.Jan.17	7:30	17:30	10:00	9:00	1:00		N
29	121	TEAM LEADER	18.Jan.17	7:00	18:00	11:00	9:00	2:00		N
30			19.Jan.17						ABSENT	N
31	121	TEAM LEADER	20.Jan.17							H
32
33
34	25			Normal Overtime ----->	39		Holiday Overtime ----->	18

_Row\^Col	B	C	D	E	F	G	H	I	J
34	TOTAL NO. OF DAYS ----->	=30-COUNTIF(K1:K31,"ABSENT")			Normal Overtime ----->	*=SUMIF(L1:L31,"N",J1:J31)24**		Holiday Overtime ----->	*=SUMIF(L1:L31,"H",J1:J31)24**

HTML After using Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4()

After from running code (HTML) :
Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4()

<b>Excel 2007 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">21.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">22.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">23.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">15:00</td><td style="text-align: right;;">8:00</td><td style="text-align: right;;"></td><td style="text-align: right;;">8:00</td><td style="text-align: right;;"></td><td style=";">H</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">24.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">25.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">26.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">27.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">17:00</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">28.Dec.16</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">29.Dec.16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ABSENT</td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">30.Dec.16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ABSENT</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">31.Dec.16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ABSENT</td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">1.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">2.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">3.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">4.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">5.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">6.Jan.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">H</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">7.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">8.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">9.Jan.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ABSENT</td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">10.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">11.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">12.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">13.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;"></td><td style="text-align: right;;">10:00</td><td style="text-align: right;;"></td><td style=";">H</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">14.Jan.17</td><td style="text-align: right;;">7:30</td><td style="text-align: right;;">17:30</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">15.Jan.17</td><td style="text-align: right;;">7:30</td><td style="text-align: right;;">17:30</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">16.Jan.17</td><td style="text-align: right;;">7:30</td><td style="text-align: right;;">17:30</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">17.Jan.17</td><td style="text-align: right;;">7:30</td><td style="text-align: right;;">17:30</td><td style="text-align: right;;">10:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">1:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;;">18.Jan.17</td><td style="text-align: right;;">7:00</td><td style="text-align: right;;">18:00</td><td style="text-align: right;;">11:00</td><td style="text-align: right;;">9:00</td><td style="text-align: right;;">2:00</td><td style="text-align: right;;"></td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">19.Jan.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">ABSENT</td><td style=";">N</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style=";">TEAM LEADER</td><td style="font-weight: bold;text-align: right;background-color: #FFFF00;;">20.Jan.17</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">H</td></tr></tbody></table><p style="width:9em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">After121</p><br /><br />

Totals output for last post ( HTML )

<b>Excel 2007 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">34</td><td style="font-weight: bold;background-color: #FFFFFF;;">TOTAL NO. OF DAYS  -----></td><td style="font-weight: bold;background-color: #FFFFFF;;">25</td><td style="font-weight: bold;;"></td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: right;;">Normal Overtime  -----></td><td style="font-weight: bold;;">39</td><td style="text-align: right;;"></td><td style="font-weight: bold;text-align: right;;">Holiday Overtime  -----></td><td style="font-weight: bold;;">18</td></tr></tbody></table><p style="width:9em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">After121</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C34</th><td style="text-align:left">=30-COUNTIF(<font color="#0000FF">K1:K31,"ABSENT"</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">G34</th><td style="text-align:left">=SUMIF(<font color="#0000FF">L1:L31,"N",J1:J31</font>)*24</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">J34</th><td style="text-align:left">=SUMIF(<font color="#0000FF">L1:L31,"H",J1:J31</font>)*24</td></tr></tbody></table></td></tr></table><br />

Top Part of Code 4 for Nelson

Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4()

This is the first part of a single code.
The second part shpuld be copied directly under the first part in the same code module

For this Post
' http://www.excelfox.com/forum/showth...iday-overtime?

Code:

'10 ' Code 4 for Nelson ' Post 27 http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10094#post10094 Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorksheetsCode4() 'http://www.excelfox.com/forum/showthread.php/2144-Code-Required-to-calculate-number-of-days-worked-normal-overtime-and-holiday-overtime?p=10078#post10078 20 Rem 1) Workbooks Info. 30 Dim Wb As Workbook ' Dim: For Object variabls: Address location to a "pointer". That has all the actual memory locations (addresses) of the various property values , and it holds all the instructions what / how to change them , should that be wanted later. That helped explain what occurs when passing an Object to a Call ed Fucntion or Sub Routine By Val ue. In such an occurance, VBA actually passes a copy of the pointer. So that has the effect of when you change things like properties on the local variable , then the changes are reflected in changes in the original object. (The copy pointer instructs how to change those values, at the actual address held in that pointer). That would normally be the sort of thing you would expect from passing by Ref erence. But as that copy pointer "dies" after the called routine ends, then any changes to the Addresses of the Object Properties in the local variable will not be reflected in the original pointer. So you cannot actually change the pointer.) 40 Set Wb = ActiveWorkbook ' Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance 50 Dim wsStear As Worksheet ' Used for each Worksheet counting Tabs from left from 1 To Total 60 Rem 2) varables for some totals ;) 70 'Const TDays As Long = 30 'Total days just taken as 30 INITIALLY ' Long is very simple to handle, - final memory "size" type is known (123.456 and 000.001 have same "size" computer memory ) , and so a Address suggestion can be given for the next line when the variable is filled in. '( Long is a Big whole Number limit (-2,147,483,648 to 2,147,483,647) If you need some sort of validation the value should only be within the range of a Byte/Integer otherwise there's no point using anything but Long.--upon/after 32-bit, Integers (Short) need converted internally anyways, so a Long is actually faster. ) 80 Dim Dte As Date, DteNo As Long ' I am hoping Dte will sort out getting a date in a format that I can use the Weekday function to see what week day it is and get that as a nuumber to check for.. 90 Rem 3) Loop through worksheets and give some Totals 100 Dim Cnt As Long ' Loop Bound variable count for going through all worksheets 110 '3a) main Loop start===================================================== 120 For Cnt = 1 To Wb.Worksheets.Count ' The Worksheets collection Object Property returns the number of worksheet items in the Workbook 130 Set wsStear = Wb.Worksheets.Item(Cnt) ' At each loop the variable is set to the current Worksheet counting from the Cnt'ths tab from left 140 Dim lr As Long ' Used for last row number in column E ( The number of “Entries” is taken as the filled dates in column E ) 150 Let lr = wsStear.Range("E" & Rows.Count & "").End(xlUp).Row ' The Range Object ( cell ) that is the last cell in the column of interest (CHOOSE a column typically that will always have a last Entry in any Data) ,( Row Number given by .Count Property applied to ( any Worksheet would do, so leaving unqualified is OK here, ) Spreadsheet Range Rows Property) has the Property .End ( argument "Looking back up" ) appled to it. This Returns a new Range ( cell ) object which is that of the first Range ( cell ) with something in it "looking back up" in the XL spreadsheet from that last Cell. Then the .Row Property is applied to return a long number equal to the Row number of that cell: Rows.Count is the very last row number in your Worksheet. It is different for earlier versions of Excel. The End(xlUp) is the same as pressing a Ctrl+UpArrow key combination. The final ".Row" returns the row where the cursor stops after moving up. 160 'Let lr = 30 ' maybe nelson means thís ? "...For all Month no. of days we take as 30 only..." For all Months, the “TOTAL NO. OF DAYS” ( to be placed in cell C34 ) is not necessarily the number of days worked. 170 Let lr = wsStear.Range("E33").End(xlUp).Row ' To allow text below row 33 180 'TOTAL NO. OF DAYS The formula for calculating this is: 190 ' _Assuming the employee is not Absent for any day, then the “TOTAL NO. OF DAYS” is always taken as 30 200 ' _ 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: 210 ' TOTAL NO. OF DAYS = 30 – ( Count of “ABSENT” ) 220 Dim FstDtaCel As Range: Set FstDtaCel = wsStear.Range("A1") 'Top Left data ' Worksheets Range(" ") Property used to return Range object of first cell in second row 230 '3b) Data arrays from worksheet. We need columns E H I J .... Date ( Column E ) and Total hrs ( Column H ) are required to use in calculations 240 Dim arrInNorm() As Variant, arrInOver() As Variant ' In the next lines the .Value2 or .Value "values" Property is applied a Range object which presents the Value or Value2 value or values in a single variable of appropriate type or a field of member Elements of varaint types.We are expecting the latter, so declare ( Dim ) a dynamic Array variable appropriately. It must be dynamic as its size will be defined at that assignment 250 Let arrInNorm() = FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 ' I ' Normal Hrs ( Column I ) are needed as they must be set to zero for Holy ?? Holidays ?? Friday ?? 260 Let arrInOver() = FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 ' J ' Overtime ( Column J ) is needed as it will be changed and then used in calculations 270 Dim arrTotHrs() As Variant ' ,' ## ' arrDteClr() As Variant 280 Let arrTotHrs() = FstDtaCel.Offset(0, 7).Resize(lr, 1).Value ' H ' ' One thing you pick up when learning VBA programming is that referring to cells from one to another via an offset is both fundamental and efficient. That makes sense as Excel is all about using the offsets mentioned above. So like if you use them you can “cut out the middle man”. ( The middle man here might be considered as, for example, in VBA, using extra variables for different Range objects: A fundamental thing to do with any cell ( or strictly speaking the Range object associated to a cell ) is the Range Item Property of any range Object, through which you can “get at” any other Range object. http://www.excelforum.com/showthread.php?t=1154829&page=13&p=4563838&highlight=#post4563838 ( It is often quicker than using a separate variable for each Range object – probably as all the variable does is hold the offset , so you might as well use the offset in the first place.. ) 290 ' Similarly Another thing you pick up along the way is that the cells ( or strictly speaking the Range objects associated with it ) can be organised into groups of cells which then are also called Range objects and are organised in their constituent parts exactly the same as for the single cell Range object. Once again this is all an indication of organising so that we get at information by sliding along a specific amount ( offset value). The Offset and Resize properties therefore return a new range object. I use the .Value 2 here as i seemed to get it for .Value anyway, not sure why yet, - so i thought be on the safe side , get it always and work somehow with that for now and convert as necerssary. Also 1 breadth Arrays due to Alan Intercept theory are held in such a ways as to be very effient in usage of values within 300 'Column L ( help column ) Column L ( help column ) 310 ' Nelson has chosen the second code. It puts formulas in cells C34, G34, and J34. 320 ' This requires “H” or “N” to indicate Holiday or Normal working day. This will be written by the code in column L 330 Dim arrL() As String 'I know the size, but must make it dynamic as Dim declaration only takes numbers, and so I use ReDim method below wehich can also take variables or formulas 340 ReDim arrL(1 To UBound(arrInNorm(), 1), 1 To 1) ' Any array first dimension ("row") will do 350 '“ABSENT” “ABSENT” ( to be written in some rows in Column K by the program ) 360 ' 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. 370 ' ( “ABSENT” is not necessarily the normal working days in which an employee is absent and / or has no total working hours. ) 380 ' “ABSENT” is to be written in column K by the code under the following criteria: 390 ' _ 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. 400 ' _ 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. 410 ' ( No consideration of this ““ABSENT” criteria thereof” is made for the case of a Holiday at the first or last “Entries” ) 420 Dim arrAbscentK() As String 'K column to have ABSCENT in for person Absent on not Holiday or Holiday written in K cloumn as ABSENT 430 ReDim arrAbscentK(1 To UBound(arrInNorm(), 1), 1 To 1) 440 'Must Loop to get interior color as this will not work. ' ## ' Let arrDteClr() = FstDtaCel.Offset(0, 4).Resize(lr, 1).Interior.Color ' because .Interior property for a Range object shows only one value for the entire range which seems to be zero unless all the cells have a colour

Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4() Part 2

For Post http://www.excelfox.com/forum/showth...0094#post10094

This is the second part os a single code.
This second part shpuld be copied directly under the first part in the same code module

Code:

'440 'Must Loop to get interior color as this will not work. ' ## ' Let arrDteClr() = FstDtaCel.Offset(0, 4).Resize(lr, 1).Interior.Color ' because .Interior property for a Range object shows only one value for the entire range which seems to be zero unless all the cells have a colour 450 Dim arrDteClr() As Double, rngDts As Range 460 Set rngDts = FstDtaCel.Offset(0, 4).Resize(lr, 1) 470 Dim Rws As Long: ReDim arrDteClr(1 To lr, 1 To 1) ' so must loop in each Interior color value 480 For Rws = 1 To UBound(arrDteClr(), 1) Step 1 'InnerLoop for dates background colors 490 Let arrDteClr(Rws, 1) = rngDts.Item(Rws, "A").Interior.Color 500 Next Rws 510 '3c) Inner loop for rows 520 Dim ShtCnt As Long ' Loop Bound Variable Count for hours columns looping 530 Dim ValidHoliday As Boolean: Let ValidHoliday = True 'Assume for now Holiday days are valid for Holiday adjustments 540 For ShtCnt = 1 To UBound(arrDteClr(), 1) Step 1 '------------------- For "rows" in data arrays 550 '3d) We need to check Interior color, and a few other things, Adjust columns I and J so that column I has no hours for holiday day and total hours goes to over time hours with criteria 9 or less than 9 hrs all total hours added overtime, 10 or above 10 hrs one hour deducted from total hours and added to column J ..... and add a H or N in helper column K 560 If arrDteClr(ShtCnt, 1) = 65535 Then ' We have a Holiday, ...but... have some other checks 570 If Not (ShtCnt = 1 Or ShtCnt = UBound(arrDteClr(), 1)) Then ' ....but... Possible futher checks for not adjusting Normal Total Hrs to overtime and remove normal Hrs 580 'It is possible to check for absent before and after current day 590 If arrTotHrs(ShtCnt - 1, 1) = Empty And arrTotHrs(ShtCnt + 1, 1) = Empty Then '...."...holiday is deducted if the person does not come the day before and after the holiday...".... To facilitate this "ABSENT" is written in column K so that 30 - CountIf ABSENT will "remove a Holiday pay" 600 Let ValidHoliday = False 610 Else 620 Let ValidHoliday = True 630 End If 640 Else 'It is not possible for absence before AND after to check for absence as one will lie in last or next month 650 End If ' We remmain at default or last set true or just set true or false 660 'We had Holiday ... 670 If ValidHoliday = True Then ' ...and all conditions for valid Holiday pay adjustments 680 'Conditions met to adjust make all of 1 less of Normal Hrs to overtime 690 If (arrTotHrs(ShtCnt, 1) * 24) <= 9 Then '(i) If Total Hrs are less than or equal to 9 ,Then all Total Hrs are added to Overtime Hrs 700 Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) ' Given To ' Added to arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1) 710 ElseIf (arrTotHrs(ShtCnt, 1) * 24) > 9 Then ' (ii) If Total Hrs are less greater than 9 , Then ( Total Hrs - 1 ) are added to Overtime Hrs 720 Let arrInOver(ShtCnt, 1) = arrTotHrs(ShtCnt, 1) - 1 / 24 ' Given To ' arrInOver(ShtCnt, 1) + arrTotHrs(ShtCnt, 1) - 1 / 24 'Added to 1 hr less overtime for more than 9 hrs worked 730 End If 740 Let arrInNorm(ShtCnt, 1) = Empty ' (iii) As array is variant type can empty Remove normal Hrs Array for(Column I) is then set tom zerow for this "row" 750 Let arrL(ShtCnt, 1) = "H" ' ' (iv)H '_-Give string, "" value of H for valid Holiday in Admin's help column 760 Else ' We had a Holiday but abscence before and after, we make in this case the AbsentK column ABSENT 770 Let arrAbscentK(ShtCnt, 1) = "ABSENT" '_- This is unusual "Abscent" case. If after and before the Holiday, the employee is absent, then the Holiday is "marked" ( in column K ) as ABSENT. This 780 Let ValidHoliday = True 'we need to reset to true 790 End If 800 Else ' No Holy Holiday 810 Let arrL(ShtCnt, 1) = "N" ' give string N for normal ' (iv)N '_-Give string, "" value of N for normal Day 820 End If 830 If arrTotHrs(ShtCnt, 1) = Empty And Not arrDteClr(ShtCnt, 1) = 65535 Then Let arrAbscentK(ShtCnt, 1) = "ABSENT" '_- column K absent days should be marked as ABSENT. This is normal Absent case for normal workdays when employee is abscent 840 '3e) ' from last code, is not now used to calculate totals 850 Next ShtCnt '--------------------------End Inner loop for rows----- 860 '3f) Paste out final Totals and days to current Worksheet 870 Let wsStear.Range("G34").Value = "=SUMIF(L1:L" & lr & ",""N"",J1:J" & lr & ")*24" 880 Let wsStear.Range("J34").Value = "=SUMIF(L1:L" & lr & ",""H"",J1:J" & lr & ")*24" 890 Let wsStear.Range("C34").Value = "=30-COUNTIF(K1:K" & lr & ",""ABSENT"")" 900 '3g) Normal Hrs ( Column I ) and Overtime Hrs ( Column J ) are changed ' And can paste out help column if you like 910 Let FstDtaCel.Offset(0, 9).Resize(lr, 1).Value2 = arrInOver() ' J ' The required spreadsheet cells range has its Range Object .Value2 values filled an allowed direct assignment to an array of values 920 Let FstDtaCel.Offset(0, 8).Resize(lr, 1).Value2 = arrInNorm() ' I 930 Let FstDtaCel.Offset(0, 11).Resize(lr, 1).Value2 = arrL() ' L 940 Let FstDtaCel.Offset(0, 10).Resize(lr, 1).Value2 = arrAbscentK() ' K 950 '3h) Set Booleans for 960 Next Cnt '==End main Loop============================================== End Sub '970 ' '980 'Rem Ref: http://www.excelfox.com/forum/showthread.php/2138-Understanding-VBA-Range-Object-Properties-and-referring-to-ranges-and-spreadsheet-cells '990 ' '1000

All Sub Folder and File List from VBA Recursion routine. Explanation and Method Comparisons

Codes required for contribution to , and to be referenced from, this Thread: http://www.excelfox.com/forum/showth...lder-Using-VBA

Theses are

_ the main initial code , ( Sub ( ) ) , used in a two code solution "recursion type" solution for Looping through all Folders and Subfolders and Files , starting from an in initial Folder which is given in this code and passed to the second code,

_ a "recursion type" code. This code successively takes a Folder, looks into its subfolders, and then passes all the subfolders successively to "itself" and repeats the process of then looking into the Sub Folders, and then passes all the subfolders successively to "itself" and repeats the process of then looking into the Sub Folders, and then passes all the subfolders successively to "itself" and repeats the process of then looking into the Sub Folders, …. Etc…..

The codes are discussed in detail at that Thread , starting from this post:

Initial Code to call the recursion code given below

Code:

'==================================== ' Dec 2017 For Python Comparison. Tutorial Post: excelforum: Tutorial Post: ExcelFox: 'http://excelpoweruser.blogspot.de/2012/04/looping-through-folders-and-files-in.html http://www.excelforum.com/excel-programming-vba-macros/1126751-get-value-function-loop-through-all-files-in-folder-and-its-subfolders.html#post4316662 http://www.excelfox.com/forum/f5/loop-through-files-in-a-folder-using-vba-1324/ Sub VBADoStuffInFoldersInFolderRecursion() 'Main routine to "Call" the first copy of the second routine, VBALoopThroughEachFolderAndItsFile( Rem 1A) Some Worksheets and General Variables Info Dim Ws As Worksheet '_-Dim: Prepares "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular "Value", or ("Values" for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post4411 Set Ws = ThisWorkbook.Worksheets.Item(1) 'Worksheets("EFFldr") 'CHANGE TO SUIT YOUR WORKSHEET '_- Set: Fill or partially Fill: Setting to a Class will involve the use of an extra New at this code line. I will then have an Object referred to as an instance of a Class. At this point I include information on my Pointer Pigeon hole for a distinct distinguishable usage of an Object of the Class. For the case of something such as a Workbook this instancing has already been done, and in addition some values are filled in specific memory locations which are also held as part of the information in the Pigeon Hole Pointer. We will have a different Pointer for each instance. In most excel versions we already have a few instances of Worksheets. Such instances Objects can be further used., - For this a Dim to the class will be necessary, but the New must be omitted at Set. I can assign as many variables that I wish to the same existing instance Ws.Range("B3:F30").ClearContents ' This line only needed for demo code Dim celTL As Range: Set celTL = Ws.Range("B3") 'Top left of where Listing should go Rem 2A) Get Folder Info Dim strWB As String ' "Pointer" to a "Blue Print" (or Form, Questionaire not yet filled in, a template etc.)"Pigeon Hole" in Memory, sufficient in construction to house a piece of Paper with code text giving the relevant information for the particular Variable Type. VBA is sent to it when it passes it. In a Routine it may be given a particular "Value", or ("Values" for Objects). There instructions say then how to do that and handle(store) that(those). At Dim the created Paper is like a Blue Print that has some empty spaces not yet filled in. A String is a a bit tricky. The Blue Print code line Paper in the Pigeon Hole will allow to note the string Length and an Initial start memory Location. This Location well have to change frequently as strings of different length are assigned. Instructiions will tell how to do this. Theoretically a specilal value vbNullString is set to aid in quich checks.. But..http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring-2.html#post44116 Let strWB = ThisWorkbook.Path & "\" & "EileensFldr" ' 'CHANGE TO SUIT if you store the main Folder to be looked through somewhere other than in the same Folder as this workbook in which the codes are in Rem 3A ) ' FileSystemObject Object Dim FSO As Object: Set FSO = CreateObject("Scripting.FileSystemObject") 'Late Binding 'Dim FSO As Scripting.FileSystemObject 'Early Binding alternative activate a reference to the Microsoft Scripting Runtime Library ( MSRL ) in the Tools References menu of VB Editor Options. 'Set FSO = New Scripting.FileSystemObject 'Create an Instance of the Class Scripting FileSystemObject Dim myFolder As Object 'An Object from myFolder, can be an declared as Dim myFolder As Folder also for Early Binding Set myFolder = FSO.GetFolder(strWB) 'Set the selected Folder to the Object Folder using this Method which takes as arbument the Full String Path Rem 4A ) Dim rCnt As Long: Let rCnt = 1: Dim CopyNumber1 As Long: Let CopyNumber1 = 1 '"Run progressin ( "down vertical" ) axis ( Row count for output ), "Down Folder chain to the right", The Count of the Copy of the called Procedue, here set to 1 for the first called copy of the second routine, which is done from this Sub( ) . Any subsequent calls of further second routine copies will be made by the current copy as it "freezes" and sets of that next copy celTL.Value = myFolder.Path: celTL.Offset(0, 1).Value = myFolder.Name: Ws.Columns("A:C").AutoFit 'First output Row '( -- Rem 5A) ) Call LoopThroughEachFolderAndItsFile(myFolder, celTL, rCnt, CopyNumber1) 'Up until now we just got the initial Folder. Now we go to all sub folders then all subfolders then all subfolders....... ' let Application.ScreenUpdating = True ' If this had been set to False earlier towards the start, as is often done, then the code might run a bit quicker by virtue of not updating the worksheet everytime an entry is made, but it is not really nacerssary unless the number of Files and Folders is massive. Even then it is probably better not to do that so that in the case of an error one has an additional way in the worksheet to see where the code stopped / errored MsgBox "All Excel Files processed", vbInformation Ws.Columns("A:H").AutoFit End Sub 'Rem 5A) --

_........

_._________________

Second code. Recursion routine

Code:

'Rem 5A) -- Sub VBALoopThroughEachFolderAndItsFile(ByVal fldFldr As Object, ByRef celTL As Range, ByRef rCnt As Long, ByVal CopyNumberFroNxtLvl As Long) 'In below function we have a nested loop to iterate each files also Dim myFldrs As Object ''This is used continuously as the "steering" thing, that is to say each Sub Folder in Folder loops, in loops, in loops......etc ....can be Dim myFldrs As Folder for early bindingDim CopyNumber As Long 'equivalent to clmLvl in Rudis Q code Dim CopyNumber As Long 'equivalent to clmLvl in Rudis Q code Let CopyNumber = CopyNumberFroNxtLvl 'This variable is local to the current running or paused copy of this routine. '5Ab) Doing stuff for current Folder For Each myFldrs In fldFldr.SubFolders 'SubFolders collection used to get at all Sub Folders ''''''''Doing stuff for each Folder, .. in this example giving '_- '_- its full path including name : and just Flder Name ' -- * Let rCnt = rCnt + 1 + 1 ''At each folder we always move down a line, and a dd amm extra line as a space between Folders ( The indication of the "column" or "down" to the right comes from the Copy Number of the Sub Procedure Let celTL.Cells(rCnt, 1).Value = myFldrs.Path: celTL.Cells(rCnt, CopyNumber).Offset(0, 2).Value = myFldrs.Name ' -- * 'Print out current Folder Path and Name in next free row. ''''''''End doing stuff for each Folder '5Ac) Doing stuff for current file. Dim oFile As Object ' ... for early binding can Dim oFile As file For Each oFile In myFldrs.Files 'Looking at all Files types initially '##### ''''''''Doing Stuff for Each File ' Dim Extension As String: Let Extension = Right(oFile.Name, (Len(oFile.Name) - (InStrRev(oFile.Name, ".")))) 'To get the bit just after the . dot. ##### ' If Left(Extension, 3) = "xls" Then 'Check for your required File Type ##### Let rCnt = rCnt + 1 celTL.Cells(rCnt, CopyNumber).Offset(0, 2).Value = oFile.Name ' Do your stuff here ' Dim wkb As Workbook On Error GoTo ErrHdlr 'In case problem opening file for example ' Set wkb = Workbooks.Open(oFile) ' wkb.Close SaveChanges:=True ' Else 'Do not do stuff for a Bad Extension ' ##### ' End If ' ##### ''''''''End Doing Sttuff for Each File NxtoFile: Next oFile ' Spring Point after error handler so as to go on to next File after the File action that errored Call LoopThroughEachFolderAndItsFile(myFldrs, celTL, rCnt, CopyNumber + 1) 'This is an example of recursion. It is actually very simple once you understand it. But it is just incredibly difficult to put in words. It is basically a Procedure that keeps calling itself as much as necessary as it goes "along", "down", or "to the right" of the Path "roots". Every time it goes off calling itself VBA runs a copy of that Procedure. It "Stacks" all info carefully for each "Copy" Run and continues to do this "drilling" down as far as it must, in this case finding the Next Folder, and then the next Folder in that, then the next Folder in that, then the next Folder in that...I think you get the point! Each time VBA makes a copy of the Routine and you go into that. The calling Routine then "freezes at its current state and all variable keep there values. The "Frozen" Routine then re starts when the copy finishes Next Exit Sub 'Normal End for no Errors Rem 6 ) Error handler section just put here for convenience ErrHdlr: 'Hopefully we know why we are here, and after informing can continue ( to next file ) MsgBox prompt:="Error " & Err.Description & " with File " & oFile & "" On Error GoTo -1 'This needs to be done to reset the VBA exceptional error state of being. Otherwise VBA "thinks" Errors are being handeled and will not respond again to the Error handler. On Error GoTo 0 ' Swiches off the current error handler. I do not really need to do this. But it is good practice so the error handler is only in place at the point where i next am expecting an error GoTo NxtoFile End Sub

_..

( Codes are also in the first Worksheet Code module of this Workbook: ( '== ' Dec 2017 For Python Comparison. https://app.box.com/s/gfuintgifu1hgw5nap3jriz2x8mp911x ) )

Dumping Logs. Recurring Excample

Dumping Logs for support of this Thread Post:
http://www.excelfox.com/forum/showth...0476#post10476

Test Function used to produce the Log below

Code:

'Going a HoldYaBackCalledYaBackClapTrapRuc - Copy number_GlobinalCntChopsLog - a few copies of this are made and run. (Recursion) '_-=Rem 4===================??? Got me hook lochprocedue in my code , 5 times simple run then another + 29 new copies of it are run = 5+30=35 times in total calling it it a few times http://www.excelfox.com/forum/showthread.php/1324-Loop-Through-Files-In-A-Folder-Using-VBA#post10421 .... wanking myself up and down a few times Private Function HoldYaBackCalledYaBackClapTrapRuc(ByVal lMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long ' ByVal CopyNumberFroNxtLvl As Long) As Long Let GlobinalCntChopsLog = GlobinalCntChopsLog + 1: Debug.Print " Going a HoldYaBackCalledYaBackClapTrapRuc"; GlobinalCntChopsLog; "(1Msg"; lMsg; ", wParam"; wParam; ", lParam"; lParam; ") Function Copy Number_"; GlobinalCntChopsLog 'If GlobinalCntChopsLog = 2 Then Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1: UnHookWindowsHookCodEx hHookTrapCrapNumber: Exit Function If lMsg = 5 Then '_-.... ( Hook type: HCBT_ACTIVATE = 5 but not here?) ... this runs a further 29 copies of HoldYaBackCalledYaBackClapTrap all coming here, so 30 times in total Debug.Print "Expose Interface"; Tab(30); GlobinalCntChopsLog SetWindowPosition wParam, 0, poX, pussY, 400, 150, 40 ' SWP_NOZORDER is 4 .. but not here?? 'SWP_NOSIZE + SWP_NOZORDER ' Pull the Chainge position ... UnHookWindowsHookCodEx hHookTrapCrapNumber ' Release the Hook 30 times this is done Else Debug.Print "No InterOfCourse"; Tab(30); GlobinalCntChopsLog; Tab(50); hHookTrapCrapNumber End If ' 5 times here then '_-.... Debug.Print "Wipe chain WRap"; Tab(30); GlobinalCntChopsLog; Tab(50); hHookTrapCrapNumber Let HoldYaBackCalledYaBackClapTrapRuc = 0 ' Done 5+30=35 times in total '0 (or False) makes it work, all other numbers and I get no Message box Let GlobinalCntChopsLog = GlobinalCntChopsLog - 1 End Function ' HoldYaBackCalledYaBackClapTrapRuc

Code:

--------------------------- MutsNuts AkaApi working ApplicationPromptToRangeInputBox --------------------------- Select Range --------------------------- OK --------------------------- WndNumber 66770 HandleWndOfMyParent 983700 hWndDskTop 66204 hHookTrapCrapNumber State of Much Such Penialtration's Number HookCodeXcretion's ================== AliAs Pull of my chain AliAs my long Hook 0 Going a HoldYaBackCalledYaBackClapTrapRuc 1 (1Msg 3 , wParam 2623104 , lParam 2353392 ) Function Copy Number_ 1 No InterOfCourse 1 276039693 Wipe chain WRap 1 276039693 Going a HoldYaBackCalledYaBackClapTrapRuc 1 (1Msg 3 , wParam 1377832 , lParam 2353500 ) Function Copy Number_ 1 No InterOfCourse 1 276039693 Wipe chain WRap 1 276039693 Going a HoldYaBackCalledYaBackClapTrapRuc 1 (1Msg 3 , wParam 3934358 , lParam 2353500 ) Function Copy Number_ 1 No InterOfCourse 1 276039693 Wipe chain WRap 1 276039693 Going a HoldYaBackCalledYaBackClapTrapRuc 1 (1Msg 3 , wParam 984706 , lParam 2353480 ) Function Copy Number_ 1 No InterOfCourse 1 276039693 Wipe chain WRap 1 276039693 Going a HoldYaBackCalledYaBackClapTrapRuc 1 (1Msg 9 , wParam 3934358 , lParam 66766 ) Function Copy Number_ 1 No InterOfCourse 1 276039693 Wipe chain WRap 1 276039693 Going a HoldYaBackCalledYaBackClapTrapRuc 1 (1Msg 5 , wParam 2623104 , lParam 2353812 ) Function Copy Number_ 1 Expose Interface 1 Going a HoldYaBackCalledYaBackClapTrapRuc 2 (1Msg 5 , wParam 2623104 , lParam 2353500 ) Function Copy Number_ 2 Expose Interface 2 Going a HoldYaBackCalledYaBackClapTrapRuc 3 (1Msg 5 , wParam 2623104 , lParam 2353188 ) Function Copy Number_ 3 Expose Interface 3 Going a HoldYaBackCalledYaBackClapTrapRuc 4 (1Msg 5 , wParam 2623104 , lParam 2352876 ) Function Copy Number_ 4 Expose Interface 4 Going a HoldYaBackCalledYaBackClapTrapRuc 5 (1Msg 5 , wParam 2623104 , lParam 2352564 ) Function Copy Number_ 5 Expose Interface 5 Going a HoldYaBackCalledYaBackClapTrapRuc 6 (1Msg 5 , wParam 2623104 , lParam 2352252 ) Function Copy Number_ 6 Expose Interface 6 Going a HoldYaBackCalledYaBackClapTrapRuc 7 (1Msg 5 , wParam 2623104 , lParam 2351940 ) Function Copy Number_ 7 Expose Interface 7 Going a HoldYaBackCalledYaBackClapTrapRuc 8 (1Msg 5 , wParam 2623104 , lParam 2351628 ) Function Copy Number_ 8 Expose Interface 8 Going a HoldYaBackCalledYaBackClapTrapRuc 9 (1Msg 5 , wParam 2623104 , lParam 2351316 ) Function Copy Number_ 9 Expose Interface 9 Going a HoldYaBackCalledYaBackClapTrapRuc 10 (1Msg 5 , wParam 2623104 , lParam 2351004 ) Function Copy Number_ 10 Expose Interface 10 Going a HoldYaBackCalledYaBackClapTrapRuc 11 (1Msg 5 , wParam 2623104 , lParam 2350692 ) Function Copy Number_ 11 Expose Interface 11 Going a HoldYaBackCalledYaBackClapTrapRuc 12 (1Msg 5 , wParam 2623104 , lParam 2350380 ) Function Copy Number_ 12 Expose Interface 12 Going a HoldYaBackCalledYaBackClapTrapRuc 13 (1Msg 5 , wParam 2623104 , lParam 2350068 ) Function Copy Number_ 13 Expose Interface 13 Going a HoldYaBackCalledYaBackClapTrapRuc 14 (1Msg 5 , wParam 2623104 , lParam 2349756 ) Function Copy Number_ 14 Expose Interface 14 Going a HoldYaBackCalledYaBackClapTrapRuc 15 (1Msg 5 , wParam 2623104 , lParam 2349444 ) Function Copy Number_ 15 Expose Interface 15 Going a HoldYaBackCalledYaBackClapTrapRuc 16 (1Msg 5 , wParam 2623104 , lParam 2349132 ) Function Copy Number_ 16 Expose Interface 16 Going a HoldYaBackCalledYaBackClapTrapRuc 17 (1Msg 5 , wParam 2623104 , lParam 2348820 ) Function Copy Number_ 17 Expose Interface 17 Going a HoldYaBackCalledYaBackClapTrapRuc 18 (1Msg 5 , wParam 2623104 , lParam 2348508 ) Function Copy Number_ 18 Expose Interface 18 Going a HoldYaBackCalledYaBackClapTrapRuc 19 (1Msg 5 , wParam 2623104 , lParam 2348196 ) Function Copy Number_ 19 Expose Interface 19 Going a HoldYaBackCalledYaBackClapTrapRuc 20 (1Msg 5 , wParam 2623104 , lParam 2347884 ) Function Copy Number_ 20 Expose Interface 20 Going a HoldYaBackCalledYaBackClapTrapRuc 21 (1Msg 5 , wParam 2623104 , lParam 2347572 ) Function Copy Number_ 21 Expose Interface 21 Going a HoldYaBackCalledYaBackClapTrapRuc 22 (1Msg 5 , wParam 2623104 , lParam 2347260 ) Function Copy Number_ 22 Expose Interface 22 Going a HoldYaBackCalledYaBackClapTrapRuc 23 (1Msg 5 , wParam 2623104 , lParam 2346948 ) Function Copy Number_ 23 Expose Interface 23 Going a HoldYaBackCalledYaBackClapTrapRuc 24 (1Msg 5 , wParam 2623104 , lParam 2346636 ) Function Copy Number_ 24 Expose Interface 24 Going a HoldYaBackCalledYaBackClapTrapRuc 25 (1Msg 5 , wParam 2623104 , lParam 2346324 ) Function Copy Number_ 25 Expose Interface 25 Going a HoldYaBackCalledYaBackClapTrapRuc 26 (1Msg 5 , wParam 2623104 , lParam 2346012 ) Function Copy Number_ 26 Expose Interface 26 Going a HoldYaBackCalledYaBackClapTrapRuc 27 (1Msg 5 , wParam 2623104 , lParam 2345700 ) Function Copy Number_ 27 Expose Interface 27 Going a HoldYaBackCalledYaBackClapTrapRuc 28 (1Msg 5 , wParam 2623104 , lParam 2345388 ) Function Copy Number_ 28 Expose Interface 28 Going a HoldYaBackCalledYaBackClapTrapRuc 29 (1Msg 5 , wParam 2623104 , lParam 2345076 ) Function Copy Number_ 29 Expose Interface 29 Going a HoldYaBackCalledYaBackClapTrapRuc 30 (1Msg 5 , wParam 2623104 , lParam 2344764 ) Function Copy Number_ 30 Expose Interface 30 Wipe chain WRap 30 276039693 Wipe chain WRap 29 276039693 Wipe chain WRap 28 276039693 Wipe chain WRap 27 276039693 Wipe chain WRap 26 276039693 Wipe chain WRap 25 276039693 Wipe chain WRap 24 276039693 Wipe chain WRap 23 276039693 Wipe chain WRap 22 276039693 Wipe chain WRap 21 276039693 Wipe chain WRap 20 276039693 Wipe chain WRap 19 276039693 Wipe chain WRap 18 276039693 Wipe chain WRap 17 276039693 Wipe chain WRap 16 276039693 Wipe chain WRap 15 276039693 Wipe chain WRap 14 276039693 Wipe chain WRap 13 276039693 Wipe chain WRap 12 276039693 Wipe chain WRap 11 276039693 Wipe chain WRap 10 276039693 Wipe chain WRap 9 276039693 Wipe chain WRap 8 276039693 Wipe chain WRap 7 276039693 Wipe chain WRap 6 276039693 Wipe chain WRap 5 276039693 Wipe chain WRap 4 276039693 Wipe chain WRap 3 276039693 Wipe chain WRap 2 276039693 Wipe chain WRap 1 276039693

_-.__________________________________

Windows Handleing Info:

Code:

' 1b) To hang in the Excel Window malking it effectively a Excel Msgbox... Normally if I did not do this ... don't do this .. that is to say leave it at 0 , specifically no window is 0 , and it "hanging in mid air so isn't even if it is imaginatively speaking Public Declare Function FindWndNumber Lib "user32" Alias "FindWindowA" (Optional ByVal lpClassName As String, Optional ByVal lpWindowName As String) As Long Dim HandleWndOfMyParent As Long ' I wanted to comment this 1b)(i) and ( 1b(ii) later ) out to leave it hanging in mid air in a virtual inadvirtual not thereness ... but somehow this complicated hook stuff does hang it somwhere but not in my Excel Window but I don't know what my parent's fart has to do with anything ' 1d) For some Misc experiments Private Declare Function FindWindowExtremeNutty Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long Dim WndNumber As Long, hWndDskTop As Long

Code:

Sub AkaApiApplicationPromptToRangeInputBox() ' This one works.. but HTF ' 1b(ii) This section is some how over written in / by the section part or some strange Addressing of HoldYaBackCalledYaBackClapTrap Let WndNumber = FindWndNumber(lpClassName:=vbNullString, lpWindowName:=vbNullString) Let HandleWndOfMyParent = FindWndNumber(lpClassName:="XLMAIN", lpWindowName:=vbNullString) ' This is a case where vbNullstring is important - that signifies that I am not giving it, which i do not have to. The second option is a bit flaky and does not often work. it certainly won't work if you make it "" as that is a specific string of zero. Null is a special idea in computing of not set yet / not defined - that is required if I do not want to give it ' 1d) Just some experiments, I forgot why as my brain has goine comfortably numb Dim HeavyWindBreak As Long: Let HeavyWindBreak = HandleWndOfMyParent Let hWndDskTop = FindWindowExtremeNutty(HandleWndOfMyParent, 0&, "XLDESK", vbNullString) Debug.Print "WndNumber"; WndNumber; " HandleWndOfMyParent"; HandleWndOfMyParent; " hWndDskTop"; hWndDskTop; " hHookTrapCrapNumber" Rem 3 Mess with me hook? God knows what this all does and it seems to make no difference if the proXYs poX or pussY are before or after SetWindowsHooksExample