Page 3 of 3 FirstFirst 123
Results 21 to 28 of 28

Thread: Appendix Thread. ( Codes for other Threads, HTML Tables, etc.)

  1. #21
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    274
    Rep Power
    3
    '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
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  2. #22
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    274
    Rep Power
    3

    Post 22 Before ( HTML )

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




    Excel 2007 32 bit
    CDEFGHIJ
    1121TEAM LEADER21.Dec.167:0018:0011:009:002:00
    2121TEAM LEADER22.Dec.167:0018:0011:009:002:00
    3121TEAM LEADER23.Dec.167:0015:008:009:000:00
    4121TEAM LEADER24.Dec.167:0018:0011:009:002:00
    5121TEAM LEADER25.Dec.167:0018:0011:009:002:00
    6121TEAM LEADER26.Dec.167:0018:0011:009:002:00
    7121TEAM LEADER27.Dec.167:0017:0010:009:001:00
    8121TEAM LEADER28.Dec.167:0018:0011:009:002:00
    929.Dec.16
    1030.Dec.16
    1131.Dec.16
    12121TEAM LEADER1.Jan.177:0018:0011:009:002:00
    13121TEAM LEADER2.Jan.177:0018:0011:009:002:00
    14121TEAM LEADER3.Jan.177:0018:0011:009:002:00
    15121TEAM LEADER4.Jan.177:0018:0011:009:002:00
    16121TEAM LEADER5.Jan.177:0018:0011:009:002:00
    17121TEAM LEADER6.Jan.17
    18121TEAM LEADER7.Jan.177:0018:0011:009:002:00
    19121TEAM LEADER8.Jan.177:0018:0011:009:002:00
    20121TEAM LEADER9.Jan.17
    21121TEAM LEADER10.Jan.177:0018:0011:009:002:00
    22121TEAM LEADER11.Jan.177:0018:0011:009:002:00
    23121TEAM LEADER12.Jan.177:0018:0011:009:002:00
    24121TEAM LEADER13.Jan.177:0018:0011:009:002:00
    25121TEAM LEADER14.Jan.177:3017:3010:009:001:00
    26121TEAM LEADER15.Jan.177:3017:3010:009:001:00
    27121TEAM LEADER16.Jan.177:3017:3010:009:001:00
    28121TEAM LEADER17.Jan.177:3017:3010:009:001:00
    29121TEAM LEADER18.Jan.177:0018:0011:009:002:00
    3019.Jan.17
    31121TEAM LEADER20.Jan.17
    32
    33
    34Normal Overtime  ----->Holiday Overtime  ----->

    121



    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  3. #23
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    274
    Rep Power
    3

    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
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  4. #24
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    274
    Rep Power
    3
    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
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  5. #25
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    274
    Rep Power
    3

    HTML After using Sub IJAdjust_LAdd_AbsentKAdd_TotalsFormulas_AllWorkshe etsCode4()

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



    Excel 2007 32 bit
    DEFGHIJKL
    1TEAM LEADER21.Dec.167:0018:0011:009:002:00N
    2TEAM LEADER22.Dec.167:0018:0011:009:002:00N
    3TEAM LEADER23.Dec.167:0015:008:008:00H
    4TEAM LEADER24.Dec.167:0018:0011:009:002:00N
    5TEAM LEADER25.Dec.167:0018:0011:009:002:00N
    6TEAM LEADER26.Dec.167:0018:0011:009:002:00N
    7TEAM LEADER27.Dec.167:0017:0010:009:001:00N
    8TEAM LEADER28.Dec.167:0018:0011:009:002:00N
    929.Dec.16ABSENTN
    1030.Dec.16ABSENT
    1131.Dec.16ABSENTN
    12TEAM LEADER1.Jan.177:0018:0011:009:002:00N
    13TEAM LEADER2.Jan.177:0018:0011:009:002:00N
    14TEAM LEADER3.Jan.177:0018:0011:009:002:00N
    15TEAM LEADER4.Jan.177:0018:0011:009:002:00N
    16TEAM LEADER5.Jan.177:0018:0011:009:002:00N
    17TEAM LEADER6.Jan.17H
    18TEAM LEADER7.Jan.177:0018:0011:009:002:00N
    19TEAM LEADER8.Jan.177:0018:0011:009:002:00N
    20TEAM LEADER9.Jan.17ABSENTN
    21TEAM LEADER10.Jan.177:0018:0011:009:002:00N
    22TEAM LEADER11.Jan.177:0018:0011:009:002:00N
    23TEAM LEADER12.Jan.177:0018:0011:009:002:00N
    24TEAM LEADER13.Jan.177:0018:0011:0010:00H
    25TEAM LEADER14.Jan.177:3017:3010:009:001:00N
    26TEAM LEADER15.Jan.177:3017:3010:009:001:00N
    27TEAM LEADER16.Jan.177:3017:3010:009:001:00N
    28TEAM LEADER17.Jan.177:3017:3010:009:001:00N
    29TEAM LEADER18.Jan.177:0018:0011:009:002:00N
    3019.Jan.17ABSENTN
    31TEAM LEADER20.Jan.17H

    After121



    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  6. #26
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    274
    Rep Power
    3
    Totals output for last post ( HTML )

    Excel 2007 32 bit
    BCDEFGHIJ
    34TOTAL NO. OF DAYS  ----->25Normal Overtime  ----->39Holiday Overtime  ----->18

    After121



    Worksheet Formulas
    CellFormula
    C34=30-COUNTIF(K1:K31,"ABSENT")
    G34=SUMIF(L1:L31,"N",J1:J31)*24
    J34=SUMIF(L1:L31,"H",J1:J31)*24

    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  7. #27
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    274
    Rep Power
    3

    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
    Last edited by DocAElstein; 02-09-2017 at 05:41 PM.
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

  8. #28
    Senior Member DocAElstein's Avatar
    Join Date
    Aug 2014
    Posts
    274
    Rep Power
    3
    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
    Google first, like this site:ExcelFox.com "Short Title or Theme of wot you’re looking for"
    Use Code Tags: Highlight code; click on the # icon above,
    Post screenshots COPYABLE to a Spredsheet; NOT IMAGES PLEASE:
    Tools for that:
    http://www.excelfox.com/forum/showth...=9821#post9821
    https://app.box.com/s/gjpa8mk8ko4vkwcke3ig2w8z2wkfvrtv
    http://excelmatters.com/excel-forums/

Similar Threads

  1. Search List of my codes
    By PcMax in forum Excel Help
    Replies: 6
    Last Post: 08-03-2014, 09:08 AM
  2. Is this codes be translated into Excel?
    By mrprofit in forum Excel Help
    Replies: 1
    Last Post: 04-12-2014, 12:19 AM
  3. compare two tables
    By emmye998 in forum Excel Help
    Replies: 1
    Last Post: 03-24-2014, 04:55 AM
  4. Replies: 0
    Last Post: 07-24-2013, 11:50 PM
  5. Correlation Map with color codes
    By Rasm in forum Excel Help
    Replies: 5
    Last Post: 12-04-2011, 08:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •