Results 1 to 10 of 10
  1. #1
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts

    UDF for Sum alpha and numeric caracter 2

    Heloo!
    Sorry that i`m starting a new topic whit the same problem ( http://windowssecrets.com/forums/sho...meric-caracter ) but I have a new case that i didn`t anticipated. I have 2 types of work program 8 hours or 7 hours.
    I insert that in column B .
    In this case i have to change the UDF. ( http://windowssecrets.com/forums/sho...meric-caracter )

    1) Z = MID(B2;1;1)*3
    2) T = MID(B2;1;1)*2
    3) N = MID(B2;1;1)
    4) if cell (numeric) from range >= 8 cell =8
    5) if cell (numeric) from range < 8 cell value
    6) If cell is blank or have another alpha character cell value = 0

    I hope i was explicit! Thanks in advance!
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this formula work in AH2? [you don't indicate on how the 8 h or 7 h play a role...]

    =COUNTIF(C2:AG2,"Z*")*3+COUNTIF(C2:AG2,"T*")*2+COU NTIF(C2:AG2,"N*")+COUNTIF(C2:AG2,">=8")*8+SUMIF(C2 :AG2,"<8")

    [I don't see the need for a UDF which requires a macro warning and is less efficient]. The values I get for rows 2-6 are: 45, 185, 162, 166, and 124 respectively.

    Steve
    PS if the 7 h or 8 h will play a role, having it as a number will aid if the number 7 and 8 will need to be used in the formula...

  3. #3
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    If the f work program is 7 hours:
    1) Z = MID(B2;1;1)*3 ( Z=7*3=21)
    2) T = MID(B2;1;1)*2 (T=7*2=14)
    3) N = MID(B2;1;1) (N=7)
    4) if cell (numeric) from range >= 8 cell =8 ( 8 will be replace with 7)
    5) if cell (numeric) from range < 8 cell value ( 8 will be replace with 7)
    6) If cell is blank or have another alpha character cell value = 0

    If the work program is 8 hours:
    1) Z = MID(B2;1;1)*3 ( Z=8*3=24)
    2) T = MID(B2;1;1)*2 (T=8*2=16)
    3) N = MID(B2;1;1) (N=8)
    4) if cell (numeric) from range >= 8 cell =8
    5) if cell (numeric) from range < 8 cell value
    6) If cell is blank or have another alpha character cell value = 0
    The TOTAL depend if the work program is 7 h or 8 h.
    Not all the employers have an 8 h work program. So in that case cell B is variable. ( MID(B2;1;1) ).I supose thait require an addition line in UDF . If i have to give more explenations please akk me. Thanks in addvance!

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    in H2:
    =LEFT(B2,1)*(COUNTIF(C2:AG2,"Z*")*3+COUNTIF(C2:AG2 ,"T*")*2+COUNTIF(C2:AG2,"N*")+COUNTIF(C2:AG2,">=8" ))+SUMIF(C2:AG2,"<8")
    Copy down the rows. I get for your example in H2:H6 the values 192, 241, 225, 182, and 250 respectively.

    What values do you get?
    Steve

  5. #5
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    The values are corectly. Can you modified this UDF to do the same thing please.All i need is column B to be a "constant".

    Public Function SumRow(rng As Range)
    Dim cell As Range
    Total = 0
    For Each cell In rng
    Select Case cell
    Case "Z"
    Total = Total + 24
    Case "T"
    Total = Total + 16
    Case "DET", "C", ""
    Total = Total
    Case "N"
    Total = Total + 6
    Case Is >= 8
    If IsNumeric(cell) Then 'Test for number
    Total = Total + 8
    End If
    Case Is < 8
    Total = Total + cell
    End Select
    Next cell
    SumRow = Total
    End Function

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Does this do what you want? In H2:
    =sumrow(C2:AG2,B2)

    and copy it down the rows

    Code:
    Option Explicit
    Public Function SumRow(rng As Range, rHr As Range)
      Dim rCell As Range
      Dim dTotal As Double
      Dim iHrs As Integer
      iHrs = Val(rHr(1))
      dTotal = 0
      For Each rCell In rng
        Select Case rCell
          Case "Z"
            dTotal = dTotal + 3 * iHrs
          Case "T"
            dTotal = dTotal + 2 * iHrs
          Case "DET", "C", ""
            dTotal = dTotal
          Case "N"
            dTotal = dTotal + iHrs
          Case Is >= iHrs
            If IsNumeric(rCell) Then 'Test for number
                dTotal = dTotal + iHrs
            End If
          Case Is < iHrs
            dTotal = dTotal + rCell
        End Select
      Next rCell
      SumRow = dTotal
    End Function
    Steve
    PS Note that the lines
    Case "DET", "C", ""
    dTotal = dTotal
    Serve no purpose and can be deleted. Any text that is not explicitly listed will be ignored and treated as zero. You don't have to explicitly list items that act as zero.
    Last edited by sdckapr; 2014-02-09 at 12:53.

  7. The Following User Says Thank You to sdckapr For This Useful Post:

    afm1985 (2014-02-09)

  8. #7
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    It`s work perfectly! Thanks sdckapr !!!

  9. #8
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Sorry bothering you, but as I progress with this employee attendance I encounter difficulties and I hope to help me!
    I realized that I have another case :
    - If "Z'' is Saturday, Sunday or in the list of days off to be equal to a value of 8 ( 1*iHr). Lists of free days are in column AF
    - If "Z" is in another situation to be equal to 16 (2*iHr).
    I attach another sheet ( Sheet1) . If there is need to more explains I will return.
    Thanks in advance!
    Attached Files Attached Files

  10. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this?

    Code:
    Option Explicit
    Public Function SumRow3(rng As Range, rHr As Range, rDates As Range, rDaysOff As Range)
      Dim x As Integer
      Dim i As Integer
      Dim dTotal As Double
      Dim iHrs As Integer
      iHrs = Val(rHr(1))
      dTotal = 0
      For x = 1 To rng.Count
        Select Case rng(x).Value
          Case "Z"
            i = 0
            On Error Resume Next
            i = Application.WorksheetFunction.Match(rDates(x), rDaysOff, 0)
            On Error GoTo 0
            If Weekday(rDates(x)) = 1 Or _
              Weekday(rDates(x)) = 7 Or i <> 0 Then
              dTotal = dTotal +  iHrs
            Else
              dTotal = dTotal + 2 * iHrs
            End If
          Case "T"
            dTotal = dTotal + 2 * iHrs
          Case "N"
            dTotal = dTotal + iHrs
          Case Is >= iHrs
            If IsNumeric(rng(x).Value) Then 'Test for number
                dTotal = dTotal + iHrs
            End If
          Case Is < iHrs
            dTotal = dTotal + rng(x).Value
        End Select
      Next x
      SumRow3 = dTotal
    End Function
    In AD3:
    =SumRow3(B3:AC3,A3,$B$1:$AC$1,$AF$2:$AF$4)

    and copy it down the column.

    Steve
    PS before in ALL cases of "Z", you had 3* hrs. Now it seems to be either 1*Hrs (on weekends or days off, or 2* hrs. Is there any time it will be 3* hrs?
    Last edited by sdckapr; 2014-02-12 at 12:20.

  11. #10
    Lounger
    Join Date
    Sep 2013
    Posts
    34
    Thanks
    16
    Thanked 0 Times in 0 Posts
    Thanks sdckapr!
    Momentary UDF it`s ok. the values are correctly. If i will encounter difficulties i will return! Thanks again!!!!!!

Posting Permissions

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