Results 1 to 10 of 10

20140209, 05:15 #1
 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...mericcaracter ) 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...mericcaracter )
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!

20140209, 08:19 #2
 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 26 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...

20140209, 08:31 #3
 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!

20140209, 11:43 #4
 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

20140209, 12:17 #5
 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

20140209, 12:50 #6
 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
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; 20140209 at 12:53.

The Following User Says Thank You to sdckapr For This Useful Post:
afm1985 (20140209)

20140209, 13:01 #7
 Join Date
 Sep 2013
 Posts
 34
 Thanks
 16
 Thanked 0 Times in 0 Posts
It`s work perfectly! Thanks sdckapr !!!

20140212, 11:26 #8
 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!

20140212, 12:17 #9
 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
=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; 20140212 at 12:20.

20140212, 16:14 #10
 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!!!!!!