# Thread: UDF for Sum alpha and numeric caracter 2

1. ## 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!

2. 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. 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. 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. 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. 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.

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

afm1985 (2014-02-09)

8. It`s work perfectly! Thanks sdckapr !!!

9. 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.

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?

11. 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
•