Updated UDF function

Topic

New Reply

[Ascaris]

Hi all,

I have one Worksheet, which is formated as  month calendar with none working days (Saturday and Sunday)  and another holidays (the holidays are set it in a range in sheet „Variabile”, and can be updated) and I have 2 user defined formulas that i need to updates it.(VBA Module)

Each cell in range are a list from data validation, and each cell have a numeric value set it allready in UDF, but the cell value need to be updated by next cell value, which can be  variable, and for all my UDF need to be upgrated with thise scenarios:

• For UDF 1 Ore_Lucrate (colum AJ):

1. if curent cell  is  SP, SL, T, Bdoc and  next cell is  Rec, Rsl, CO,  CM  the cell value to be 25.
2. if curent cell is  SP,  SL, T, Bdoc and  next cell is SP,  SL, T, Bdoc  or is numeric the cell value to be 24.

• For UDF 2 ore_Ms (colum AL):

1. if curent cell is  “SP”, “SL”, “T”, “Bdoc” is the day before a holidays, the cell value to be 9
2. When cell Value is a number and it is Sunday value is not sum it.

I attached an example sheet.[mod note: file was deleted automatically by site; file type not permitted]

Thank you in advance and I hope I  was explicit.

 

• This topic was modified 1 year, 4 months ago by Ascaris.

Reply | Quote

Replies

Zip the file and edit your post to upload.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

WSafm1985

Reply | Quote

Mr RetiredGeek any solutions? Or it is not posibile what I ask?

Reply | Quote

If you cannot edit your post (there is a time limit for editing), you can start a new reply and upload the .zip file to it.

At the bottom of the Reply, under “Attachments,” Select File. In Explorer that pops up, choose “Open”

Reply | Quote

I attached an example sheet.

Reply | Quote

Anyone can help me please with updatin my user define function?

Reply | Quote

As I don’t have Excel I’ve posted the code instead of the spreadsheet.

Here is ore_lucrate.
Changed iHrs, you only need the value.
Added a test for the next cell value.
Replaced the 1st numeric test with a Case Else.

BTW, the result of row 15 should be 75 according to your description, not 45.

cheers, Paul

Public Function ore_lucrate(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)
	dTotal = 0
	For x = 1 To Rng.Count
		Select Case Rng(x).Value
		Case "SP", "SL", "T", "Bdoc"  ', "Cont", "Int"
			dTotal = dTotal + 3 * iHrs
			If x < Rng.Count Then
				Select Case Rng(x+1).Value
				Case "Rec", "Rsl", "CO", "CM"
					dTotal = dTotal + 1
				End Select
			End If
		Case Else
			If IsNumeric(Rng(x).Value) Then 'Test for number
				dTotal = dTotal + Rng(x).Value
			End If
		End Select
	Next x
	ore_lucrate = dTotal
End Function

Reply | Quote

I don’t understand your description of ore_Ms.

Assuming 5/1 is a holiday, any value in 4/1 should be 9? Or 9 x 3?

If 7/1 (Sunday) is SP, what value should it have and how do you “value it not sum it”?

cheers, Paul

Reply | Quote

Mr. Paul T  if 5/1 is a holiday, value in 4/1 should be 9.

And for the Sunday, if is numeric just to sum the cell value.

When is Saturday or Holiday I use this line and is working

If Weekday(rDates(x)) = 7 Or i <> 0 Then ‘ 7 este sambata

dTotal = dTotal + Rng(x).Value

 

Reply | Quote

I have assumed any day before a holiday = 9. Is that correct? What if Sunday is before a holiday?

Here is ORE_MS based on my assumption that only values are added, text is not.

cheers, Paul

Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
  Dim x As Integer
  Dim i As Integer
  Dim dTotal As Double

  dTotal = 0
  For x = 1 To Rng.Count
 		Select Case Rng(x).Value
	Case "SP", "SL", "T", "Bdoc"
        If Application.WorksheetFunction.Match(rDates(x+1), rDaysOff, 0) > 0 Then  
        	' not day before holiday
        	dTotal = dTotal + 9 ' day before a holiday
        End If
Case Else
		If IsNumeric(Rng(x).Value) Then 'Test for number
         	dTotal = dTotal + Rng(x).Value
	    End If
    End Select
     Next x
  ore_Ms = dTotal
End Function

Reply | Quote

BTW, I created a global range named “Holidays”, defined as $Variabile.$L$1:$L$42
Then the formula in AJ15 would be =ORE_LUCRATE(E15:AI15,D15,$E$13:$AI$13,Holidays)
In AH15 its =ORE_MS(E15:AI15,$E$13:$AI$13,Holidays)

cheers, Paul

Reply | Quote

Mr. Paul T, I will tried to be more specific. I belive what i wish is a combination between  what I have allread and what you wrote to me. I put down my code with some notes and what I wish i will color it whit red:

Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
Dim x As Integer
Dim i As Integer
Dim dTotal As Double
dTotal = 0
For x = 1 To Rng.Count
Select Case Rng(x).Value
Case “SP”, “SL”, “T”, “Bdoc”
i = 0
On Error Resume Next
i = Application.WorksheetFunction.Match(rDates(x), rDaysOff, 0)
On Error GoTo 0
<span style=”color: #008000;”>‘******************** IF is SATURADAY or HOLIDAY =25 corect</span>
If Weekday(rDates(x)) = 7 Or i <> 0 Then
dTotal = dTotal + 25
Else
dTotal = dTotal + 0
End If
On Error GoTo 0
<span style=”color: #008000;”>‘******************** IF is SUNDAY = 16 corect</span>
If Weekday(rDates(x)) = 1 Then
dTotal = dTotal + 16
End If
<span style=”color: #008000;”>‘******************** IF is FRIDAY =9 corect</span>
If Weekday(rDates(x)) = 6 Then
dTotal = dTotal + 9
End If

‘*************** From this line I need help!!!
<span style=”color: #ff0000;”>’If is day before holiday cell value is 9</span>
If Application.WorksheetFunction.Match(rDates(x + 1), rDaysOff, 0) > 0 Then
Total = dTotal + 9 ‘ day before a holiday
End If
<span style=”color: #ff0000;”>’If is number and is SATURADAY, SUNDAY or HOLIDAY dTotal = dTotal + Rng(x).Value</span>
Case Else
If IsNumeric(Rng(x).Value) Then ‘Test for number
dTotal = dTotal + Rng(x).Value
End If
End Select
Next x
ore_Ms = dTotal
End Function

If Sunday is before holiday or are to holiday consecutive, cell value should be 25! A forgot that.

Cheers

Reply | Quote

Saturday or Holiday or Sunday before Holiday = 25
Sunday = 16
Friday = 9
Day before a holiday = 9 except if above

Is that correct?

cheers, Paul

Reply | Quote

Mr. Paul T,

It is correct.

Reply | Quote

Latest ore_MS.

cheers, Paul

'##############################
'Saturday or Holiday or Sunday before Holiday = 25
'Sunday = 16
'Friday = 9
'Day before a holiday = 9 except if above


Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
  Dim x As Integer
  Dim i As Integer
  Dim dTotal As Double

  dTotal = 0
  For x = 1 To Rng.Count 
  	if Rng(x).Value <> "" Then 
        If WeekDay(rDates(x)) = 7 Then  
        	dTotal = dTotal + 25 '  Saturday
        ElseIf WeekDay(rDates(x)) = 1 And Application.WorksheetFunction.Match(rDates(x + 1), rDaysOff, 0) > 0 Then  
        	dTotal = dTotal + 25 '  Sunday before a holiday
        ElseIf Application.WorksheetFunction.Match(rDates(x), rDaysOff, 0) > 0 Then  
        	dTotal = dTotal + 25 '  Holiday
        ElseIf WeekDay(rDates(x)) = 7 Then  
        	dTotal = dTotal + 16 '  Sunday
        ElseIf WeekDay(rDates(x)) = 6 Then  
        	dTotal = dTotal + 9 '  Friday
        ElseIf IsNumeric(Rng(x).Value) Then 'Test for number
         	dTotal = dTotal + Rng(x).Value
	    End If
	End If        
  Next x
  ore_Ms = dTotal
End Function

Reply | Quote

 

And no select case?  Case “SP”, “SL”, “T”, “Bdoc”?

And if cell value is not “SP”, “SL”, “T”, “Bdoc and it is another symbol should be ignore, not to crush the function!

Or I did not understood were to copy your pice of VBA!

 

Reply | Quote

We do not need those values because they do not give us any information, other than “this day is to be included in the calculation”. For that I check if the cell has a value:if Rng(x).Value <> ""

You could also use if IsEmpty(Rng(x).Value) = False

cheers, Paul

 

Reply | Quote

Ah, you need to ignore the cell if it isn’t one of those 4.

Back soon.

Reply | Quote

This should do the trick.

cheers, Paul

'##############################
' if =  "SP", "SL", "T", "Bdoc" or number
'Saturday or Holiday or Sunday before Holiday = 25
'Sunday = 16
'Friday = 9
'Day before a holiday = 9 except if above


Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
	Dim x As Integer
	Dim i As Integer
	Dim dTotal As Double
	Dim CellVal

	dTotal = 0
	For x = 1 To Rng.Count
		CellVal =  Rng(x).Value
		If not IsEmpty(CellVal) Then 'change to something we can use in the Case statement
			If IsNumeric(CellVal) Then
				CellVal = "True" 
			end if
		end if
		Select Case CellVal
			Case  "SP", "SL", "T", "Bdoc", "True"
			If WeekDay(rDates(x)) = 7 Then  
				dTotal = dTotal + 25 '  Saturday
			ElseIf WeekDay(rDates(x)) = 1 And Application.WorksheetFunction.Match(rDates(x + 1), rDaysOff, 0) > 0 Then  
				dTotal = dTotal + 25 '  Sunday before a holiday
			ElseIf Application.WorksheetFunction.Match(rDates(x), rDaysOff, 0) > 0 Then  
				dTotal = dTotal + 25 '  Holiday
			ElseIf WeekDay(rDates(x)) = 7 Then  
				dTotal = dTotal + 16 '  Sunday
			ElseIf WeekDay(rDates(x)) = 6 Then  
				dTotal = dTotal + 9 '  Friday
			ElseIf IsNumeric(Rng(x).Value) Then 'Test for number
				dTotal = dTotal + Rng(x).Value
			End If
		End Select
	Next x

  ore_Ms = dTotal
End Function

Reply | Quote

I test it and the result is #VALUE!

I attached the sample.

Something is broke or I am doing something wrong. I put the UDF in column AL =ore_Ms(E15:AK15;$E$13:$AI$13;Variabile!L2:L16)

Reply | Quote

The sheet you attached has the old functions.
I put the new ore_MS in and it works as expected.

As you have Excel and I have LibreOffice there may be some differences in the code behaviour, but I tried to make it generic.

If you open the macro editor you should be able to step through the code to see where the error is.

cheers, Paul

Reply | Quote

First of all, thank you for being patient with me. I’m a beginner in VBA and maybe I’m not doing the right thing. The previously uploaded file was an erroneous one. What steps did I take:

I copied your code in Module2 in VBA. The result is the same, it shows me #Value!. I have also attached the file with a screenshot of the result. I put in the AM column some additional comments with the correct result and some observations

If you have the possibility to upload youre excel file with your result, maybe it would help me to see where I am wrong.

I’m sure it will be resolved, but the time zone difference and my inexperience make it take longer.

UDF should work on computers running Excel 2007 and 2021. I do not know and have not used LibreOffice and if this is the reason why we see different results.

cheers, afm1985

Reply | Quote

Paul T wrote:

Saturday or Holiday or Sunday before Holiday = 25 Sunday = 16 Friday = 9 Day before a holiday = 9 except if above Is that correct?

This is not correct per your latest sheet.

Is this correct?

if “SP”, “SL”, “T”, “Bdoc” or number
if number, use number
else
Saturday or Holiday or Sunday before Holiday = 25
Sunday = 16
Friday = 9
Day before a holiday = 9 except if above

What if a day with a number is before a holiday? Is it 9, or the number if it’s more than 9?

cheers, Paul

Reply | Quote

The main problem with code for UDF code was that due to calculation it showed me (VALUE#!) and  I could not figure out the final values ​​collected. Maybe I could edit it myself.

Maybe I didn’t express myself correctly, so I’ll tell you the individual values ​​for each symbol for each situation that I identified:

-if “SP”, “SL”, “T”, “Bdoc” or number

if number, use number

else

-Saturday or Holiday or Sunday before Holiday = 25

-Sunday or Holiday before a working day = 16 (Exemple is date 2/1 which is a Holiday and next day is a working one.

-Friday = 9

-Day before a holiday = 9 except if above

-If a day with a number is before holiday is nothing (ignore it or value is 0)

-and if are another simbol except “SP”, “SL”, “T”, “Bdoc” is nothing( ignore it or value is 0)

– and can not figure yet  if i will have error when a holiday it is Sunday or Saturday but the value above remain.

I test you last UDF with “ISNA”  and it same problem: #VALUE! with next notice, maybe it is Help you:

-when I complete day 1/1 with symbol  is working is correct the result is 25,  if i insert in next cell another Symbol is #VALUE! And if I insert a number after day 1/1,  (2/1)  with is correct.

-when I insert a number in a non-working  day 3/1  should be ignored.

And line  Dim CellVal without nothing, as double, as integer, or something else is correct?

 

Reply | Quote

Here is the correct version that works in LibreOffice.

'##############################
'if =  "SP", "SL", "T", "Bdoc" or number
'if number then = number
'else
'Saturday or Holiday or Sunday before Holiday = 25
'Sunday = 16
'Friday = 9
'Day before a holiday = 9 except if above


Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
    Dim x As Integer
    Dim i As Integer
    Dim dTotal As Double
    Dim CellVal

    dTotal = 0
    For x = 1 To Rng.Count
        CellVal = Rng(x).Value
        If Not IsEmpty(CellVal) Then 'change to something we can use in the Case statement
            If IsNumeric(CellVal) Then
                CellVal = "True"
            End If
        End If
        Select Case CellVal
            Case "SP", "SL", "T", "Bdoc", "True"
	        If IsNumeric(Rng(x).Value) Then 'Test for number
                dTotal = dTotal + Rng(x).Value
           	ElseIf Weekday(rDates(x)) = 7 Then
                dTotal = dTotal + 25 '  Saturday
            ElseIf Weekday(rDates(x)) = 1 And Application.WorksheetFunction.Match(rDates(x + 1), rDaysOff, 0) > 0 Then
                dTotal = dTotal + 25 '  Sunday before a holiday
            ElseIf Application.WorksheetFunction.Match(rDates(x), rDaysOff, 0) > 0Then
                dTotal = dTotal + 25 '  Holiday
            ElseIf Application.WorksheetFunction.Match(rDates(x+1), rDaysOff, 0) > 0 Then
                dTotal = dTotal + 9 '  Day before a Holiday
            ElseIf Weekday(rDates(x)) = 1 Then
                dTotal = dTotal + 16 '  Sunday
            ElseIf Weekday(rDates(x)) = 6 Then
                dTotal = dTotal + 9 '  Friday
            End If
        End Select
    Next x

  ore_Ms = dTotal
End Function

Reply | Quote

This version has “ISNA” statements to catch Excel MATCH results and may fix the errors you are getting.

'##############################
'if =  "SP", "SL", "T", "Bdoc" or number
'if number then = number
'else
'Saturday or Holiday or Sunday before Holiday = 25
'Sunday = 16
'Friday = 9
'Day before a holiday = 9 except if above


Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
    Dim x As Integer
    Dim i As Integer
    Dim dTotal As Double
    Dim CellVal

    dTotal = 0
    For x = 1 To Rng.Count
        CellVal = Rng(x).Value
        If Not IsEmpty(CellVal) Then 'change to something we can use in the Case statement
            If IsNumeric(CellVal) Then
                CellVal = "True"
            End If
        End If
        Select Case CellVal
            Case "SP", "SL", "T", "Bdoc", "True"
	        If IsNumeric(Rng(x).Value) Then 'Test for number
                dTotal = dTotal + Rng(x).Value
           	ElseIf Weekday(rDates(x)) = 7 Then
                dTotal = dTotal + 25 '  Saturday
            ElseIf Weekday(rDates(x)) = 1 And Not Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(rDates(x + 1), rDaysOff, 0)) Then
                dTotal = dTotal + 25 '  Sunday before a holiday
            ElseIf Not Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(rDates(x), rDaysOff, 0)) Then
                dTotal = dTotal + 25 '  Holiday
            ElseIf Not Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(rDates(x+1), rDaysOff, 0)) Then
                dTotal = dTotal + 9 '  Day before a Holiday
            ElseIf Weekday(rDates(x)) = 1 Then
                dTotal = dTotal + 16 '  Sunday
            ElseIf Weekday(rDates(x)) = 6 Then
                dTotal = dTotal + 9 '  Friday
            End If
        End Select
    Next x

  ore_Ms = dTotal
End Function

Reply | Quote

Maybe adding “On Error Resume Next” immediately after the function name will fix it?

WSafm1985 wrote:

Dim CellVal without nothing

That is correct. We don’t know if it will be text or number so we leave it as the default of Variant.
BTW, you don’t need dTotal as Double, Integer is fine – they are whole numbers.

cheers, Paul

Reply | Quote

I tried adding a line “On Error Resume Next”,  not working, the same result #VALUE#!.I was left without solutions. Have you tested the UDF on an Office Excel workbook?

Or maybe someone on the forum can help us whit a test an can figure out!. I’m sure a solution will be found and guaranteed it’s something simple that we’re missing! Do you have any other suggestions for me?

Reply | Quote

Does ore_lucrate work?
If so I’ll rehash ore_MS to use the same functions.

cheers, Paul

Reply | Quote

Ore_lucrate work  fine whithout any error.

I couldn’t get into the function with F8-Step into- in a Public Function.

I will google it how to do it and if I succeed, I’ll come back with comments on the code

But if it help you meanwhile, ore_lucrate work fine. Maybe can be  rehash.

Reply | Quote

Can you test the code by commenting out the test lines one at a time until it works?
Adding an apostrophe to each of the “ElseIf” statements will do the trick.

cheers, Paul

Reply | Quote

WSafm1985 wrote:

I couldn’t get into the function with F8

No need.
Comment the code and then change a value in the spreadsheet. Do this until you have commented out all the ElseIf lines.

cheers, Paul

Reply | Quote

I added the comments to each Else. From what I realized, the problem is that they don’t work together. I hope you will find a solution.
Holiday before a working day = 16 (Exemple is date 2/1 which is a Holiday and next day is a working one.
-If a day with a number is before holiday is nothing (ignore it or value is 0)
-and if are another simbol except “SP”, “SL”, “T”, “Bdoc” is nothing( ignore it or value is 0)

'##############################
'if =  "SP", "SL", "T", "Bdoc" or number
'if number then = number
'else
'Saturday or Holiday or Sunday before Holiday = 25
'Sunday = 16
'Friday = 9
'Day before a holiday = 9 except if above
Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
    Dim x As Integer
    Dim i As Integer
    Dim dTotal As Double
    Dim CellVal
    dTotal = 0
    For x = 1 To Rng.Count
        CellVal = Rng(x).Value
        If Not IsEmpty(CellVal) Then 'change to something we can use in the Case statement
            If IsNumeric(CellVal) Then
                CellVal = "True"
            End If
        End If
        Select Case CellVal
            Case "SP", "SL", "T", "Bdoc", "True"
            If IsNumeric(Rng(x).Value) Then 'Test for number Comment: Here, including working days. It should only be on Saturdays, Sundays and holidays
                dTotal = dTotal + Rng(x).Value
            ElseIf Weekday(rDates(x)) = 7 Then '  Comment: The Value from Saturday is correct 25 and it

s working toghether with Sunday and Friday and Isnumeric
dTotal = dTotal + 25 ‘ Saturday
ElseIf Weekday(rDates(x)) = 1 And Not Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(rDates(x + 1), rDaysOff, 0)) Then ‘ Comment: The Value from Sunday before a holiday is correct 25. NOT working toghether with NOTHING
dTotal = dTotal + 25 ‘ Sunday before a holiday
ElseIf Not Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(rDates(x), rDaysOff, 0)) Then ‘ Comment: The Value from Holiday is correct 25. NOT working toghether with NOTHING
dTotal = dTotal + 25 ‘ Holiday
ElseIf Not Application.WorksheetFunction.IsNA(Application.WorksheetFunction.Match(rDates(x + 1), rDaysOff, 0)) Then ‘ Comment: The Value from day before Holiday is correct 9. NOT working toghether with NOTHING
dTotal = dTotal + 9 ‘ Day before a Holiday
ElseIf Weekday(rDates(x)) = 1 Then ‘ Comment: The Value from Sunday is correct 16 and it`s working toghether with Friday, Saturday and Isnumeric
dTotal = dTotal + 16 ‘ Sunday
ElseIf Weekday(rDates(x)) = 6 Then ‘ Comment: The Value from Friday are correct 16 and it`s working toghether with Sunday, Saturday and Isnumeric
dTotal = dTotal + 9 ‘ Friday
End If
End Select
Next x
ore_Ms = dTotal
End Function`

Reply | Quote

Here is a version with the Match test replaced by a home made match.

'##############################
'if =  "SP", "SL", "T", "Bdoc" or number
'if number then = number
'else
'Saturday or Holiday or Sunday before Holiday = 25
'Sunday = 16
'Friday = 9
'Day before a holiday = 9 except if above


Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
    Dim x As Integer
    Dim i As Integer
    Dim dTotal As Double
    Dim CellVal As Variant 'could be text or integer

    dTotal = 0
    For x = 1 To Rng.Count
        CellVal = Rng(x).Value
        If Not IsEmpty(CellVal) Then 'change to something we can use in the Case statement
            If IsNumeric(CellVal) Then
                CellVal = "True"
            End If
        End If
        Select Case CellVal
            Case "SP", "SL", "T", "Bdoc", "True"
	        If IsNumeric(Rng(x).Value) Then 'Test for number
                dTotal = dTotal + Rng(x).Value
           	ElseIf Weekday(rDates(x).Value) = 7 Then
                dTotal = dTotal + 25 '  Saturday
            ElseIf Weekday(rDates(x).Value) = 1 And oreMatch(rDates(x + 1).Value, rDaysOff) Then
                dTotal = dTotal + 25 '  Sunday before a holiday
            ElseIf oreMatch(rDates(x).Value, rDaysOff) Then
                dTotal = dTotal + 25 '  Holiday
            ElseIf oreMatch(rDates(x+1).Value, rDaysOff) Then
                dTotal = dTotal + 9 '  Day before a Holiday
            ElseIf Weekday(rDates(x).Value) = 1 Then
                dTotal = dTotal + 16 '  Sunday
            ElseIf Weekday(rDates(x).Value) = 6 Then
                dTotal = dTotal + 9 '  Friday
            End If
        End Select
    Next x

  ore_Ms = dTotal
End Function

Private Function oreMatch(Str As String, Arr As Range) As Boolean
	Dim x As Integer
	Dim b As Integer
	b = 0
	For x = 1 To Arr.Count
		if Arr(x).Value = Str Then b = 1
		if b then Exit For
	Next x
	oreMatch = b
End Function

Reply | Quote

Mr Paul no more #VALUE# error! It works almost perfectly. I have the following requests and I’m done:

1. Holiday before a working day = 16 (like a Sunday)  (Exemple is date 2/1 which is a Holiday and next day is a working one.
2. And the numeric characters from working days should not be counted/added. They should be ignored, value. Only  in Saturday or Holiday or Sunday.

Thank you for your patience and I appreciate your help.

 

Reply | Quote

That is a really complex set of requirements. No wonder you need a macro to work it out.
Back soon.

cheers, Paul

Reply | Quote

A holiday = 25. When does it change to 16?

cheers, Paul

Reply | Quote

When the Holiday is  before a working day .  Exemple: Date  02 January 2024  from  worksheet! 

Reply | Quote

Do you mean when you enter “SP”, “SL”, “T”, “Bdoc” or number immediately after a holiday, then holiday = 16?

A holiday with “SP”, “SL”, “T”, “Bdoc” or number with a blank day after = 25
A holiday with “SP”, “SL”, “T”, “Bdoc” or number with a NON blank day after = 16

cheers, Paul

Reply | Quote

In the last version of the function you posted, I only want two small improvements. The rest works perfectly
1. When there are 2 legal holidays next to each other and in the second legal holiday enter “SP”, “SL”, “T”, “Bdoc” the value = 16

2.The numerical characters must be collected only on Saturdays, Sundays and legal holidays. I don’t want them to collect when I put them in during the week. Through my function I want to calculate the time that is paid twice. Double work is paid on Saturdays, Sundays and legal holidays.
And there is the possibility to work only a few hours and not a whole shift which is calculated with the maximum number of hours that we set at “SP”, “SL”, “T”, “Bdoc”.

Maybe the details will help you understand what I want from the position. Thank you in advance

Reply | Quote

Here is what should be the final version. 🙂
I have allowed for a holiday between 2 holidays – 3 in a row. 25+25+16

cheers, Paul

'##############################
'if =  "SP", "SL", "T", "Bdoc" or number

'if number and (Holiday, Saturday or Sunday) then = number
'If
'Holiday between Holidays = 25
'Holiday following Holiday = 16
'Saturday or Holiday or Sunday before Holiday = 25
'Day before a Holiday = 9
'Sunday = 16
'Friday = 9


Public Function ore_Ms(Rng As Range, rDates As Range, rDaysOff As Range)
    Dim x As Integer
    Dim i As Integer
    Dim dTotal As Double
    Dim CellVal As Variant 'could be text or integer

    dTotal = 0
    For x = 1 To Rng.Count
        CellVal = Rng(x).Value
        If Not IsEmpty(CellVal) Then 'change to something we can use in the Case statement
            If IsNumeric(CellVal) Then
                CellVal = "True"
            End If
        End If
        Select Case CellVal
        Case "True"  'numbers only
	        If IsNumeric(Rng(x).Value) And (oreMatch(rDates(x).Value, rDaysOff) Or Weekday(rDates(x).Value) = 1 Or Weekday(rDates(x).Value) = 7) Then
                dTotal = dTotal + Rng(x).Value
            End If
        Case "SP", "SL", "T", "Bdoc"
           	If Weekday(rDates(x).Value) = 7 Then
                dTotal = dTotal + 25 '  Saturday
            ElseIf Weekday(rDates(x).Value) = 1 And oreMatch(rDates(x + 1).Value, rDaysOff) Then
                dTotal = dTotal + 25 '  Sunday before a holiday
            ElseIf oreMatch(rDates(x).Value, rDaysOff) Then
            	If oreMatch(rDates(x-1).Value, rDaysOff) And oreMatch(rDates(x+1).Value, rDaysOff) Then 
            		dTotal = dTotal + 25 '  Holiday between two Holidays
            	ElseIf oreMatch(rDates(x-1).Value, rDaysOff) Then 
            		dTotal = dTotal + 16 '  Holiday following Holiday
            	Else
	                dTotal = dTotal + 25 '  Holiday
	            EndIf
            ElseIf oreMatch(rDates(x+1).Value, rDaysOff) Then
                dTotal = dTotal + 9 '  Day before a Holiday
            ElseIf Weekday(rDates(x).Value) = 1 Then
                dTotal = dTotal + 16 '  Sunday
            ElseIf Weekday(rDates(x).Value) = 6 Then
                dTotal = dTotal + 9 '  Friday
            End If
        End Select
    Next x

  ore_Ms = dTotal
End Function

Private Function oreMatch(Str As String, Arr As Range) As Boolean
	Dim x As Integer
	Dim b As Integer
	b = 0
	For x = 1 To Arr.Count
		if Arr(x).Value = Str Then b = 1
		if b then Exit For
	Next x
	oreMatch = b
End Function

2 users thanked author for this post.

zeddy, WSafm1985

Reply | Quote

 

Thank you Mr Paul, it's magic. 
I appreciate your patience and I hope you will help me with my next project.
 Good luck in everything you want.

1 user thanked author for this post.

zeddy

Reply | Quote