help with UDF Function

[jonsonbero]

Hi Everyone,
I have the following UDF Function that works well ... but Can I ask for assistance on Add a fourth case with a name partpension30
To get the correct result for this case as illustrated in the attachment and The correct result for this case is = 168.66
Please have a look at the sample workbook so as to see the value In the cell E6
Here is what I currently have in my Module...Thanks in advance for any assistance.

[Paul_Hossler]

I had to reformat your UDF somewhat so that I could follow it

But at least I got the same answers you did




Option Explicit


Function MyUDF(Condition1 As String, Condition2 As String, TheVal As Double, EvacDate As Date) As Variant
Dim StartDate As Date, AfterDate As Date, EndDate As Date
Dim Factor1 As Double, Factor2 As Double
Dim Duration As Long

StartDate = DateSerial(Year(Date), Month(Date), 1)
EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
Duration = EvacDate - StartDate

Factor1 = TheVal / 31#
If IsNumeric(Right(Condition1, 2)) Then
Factor2 = CDbl(Right(Condition1, 2)) / 100#
Else
Factor2 = 1#
End If


Select Case LCase(Condition2)

Case "one", "two", "three"

Select Case LCase(Condition1)
Case "on the job"
MyUDF = TheVal
Case "ending service"
MyUDF = Factor1 * Duration
Case "death"
MyUDF = Factor1 * (Duration + 1)
Case Else
If Condition1 Like "partdeath*" Then
MyUDF = Factor2 * Factor1 * (Duration + 1)
ElseIf Condition1 Like "partpension*" Then
MyUDF = Factor2 * Factor1 * Duration
ElseIf Condition1 Like "part*" Then
MyUDF = Factor2 * TheVal
ElseIf Condition1 Like "cut*" Then
MyUDF = TheVal * Factor2 / 31# * (EvacDate - StartDate + 1) + Factor1 * (EndDate - EvacDate)
End If
End Select

Case Else
MyUDF = CVErr(xlErrNA)
End Select


End Function




Edit -- added logic for other than 30%, 'part30', 'cut30', etc.

[jonsonbero]

Thanks for your reply Mr. Paul_Hossler
This is if the number of days in all months 31 days ... Results vary according to the number of days the maturity month
I can provide additional clarification if needed ...Thanks in advance

[Paul_Hossler]

Thanks for your reply Mr. Paul_Hossler
This is if the number of days in all months 31 days ... Results vary according to the number of days the maturity month
I can provide additional clarification if needed ...Thanks in advance

1. Your example only had 31 days/month in it. No mention of an adjustment

2. 'Maturity' month is never used in the spreadsheet. I'm guessing it's supposed to be the number of days in the month of the the EvacDate

3. I left these 2 lines in (1/1/2021), but changed EvacDate to 2/13/2021, and I didn't like the results (looked funny)



StartDate = DateSerial(Year(Date), Month(Date), 1)
EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)


4. Are StartDate and EndDate supposed to be the first and last days of the EvacDate?


It's a real time saver if you include all the information/business rules and use consistent terminology from the beginning



Option Explicit

Function MyUDF(Condition1 As String, Condition2 As String, TheVal As Double, EvacDate As Date) As Variant
Dim StartDate As Date, AfterDate As Date, EndDate As Date
Dim Factor1 As Double, Factor2 As Double
Dim Duration As Long, NumDaysInEvacMonth As Long

StartDate = DateSerial(Year(Date), Month(Date), 1)
EndDate = DateSerial(Year(Date), Month(Date) + 1, 0)
Duration = EvacDate - StartDate
NumDaysInEvacMonth = Day(DateSerial(Year(EvacDate), Month(EvacDate) + 1, 0))


Factor1 = TheVal / NumDaysInEvacMonth
If IsNumeric(Right(Condition1, 2)) Then
Factor2 = CDbl(Right(Condition1, 2)) / 100#
Else
Factor2 = 1#
End If


Select Case LCase(Condition2)

Case "one", "two", "three"

Select Case LCase(Condition1)
Case "on the job"
MyUDF = TheVal
Case "ending service"
MyUDF = Factor1 * Duration
Case "death"
MyUDF = Factor1 * (Duration + 1)
Case Else
If Condition1 Like "partdeath*" Then
MyUDF = Factor2 * Factor1 * (Duration + 1)
ElseIf Condition1 Like "partpension*" Then
MyUDF = Factor2 * Factor1 * Duration
ElseIf Condition1 Like "part*" Then
MyUDF = Factor2 * TheVal
ElseIf Condition1 Like "cut*" Then
MyUDF = TheVal * Factor2 / NumDaysInEvacMonth * (EvacDate - StartDate + 1) + Factor1 * (EndDate - EvacDate)
End If
End Select

Case Else
MyUDF = CVErr(xlErrNA)
End Select


End Function

[jonsonbero]

Thanks Mr. Paul_Hossler for your solution is amazing and works like magic.
Just a little point need to ask for .. Column B contains some names like one, two, three
How can make the output blank instead of zeros If the same column contains other names?
Thanks again.

[Paul_Hossler]

Change



Case Else
MyUDF = CVErr(xlErrNA) '<<<<<<<<<<<<<<<<<<<<<
End Select





to




Case Else
MyUDF = vbNullString '<<<<<<<<<<<<<<<<<<<<<
End Select

[jonsonbero]

Thanks Mr. Paul_Hossler for your insight and that's ok
Column B contains some names like one, two, three ... I want to give each name a specific percentage plus a specified amount
My question is, How can Multiplied by value of column C by the specified percentage Plus the specified amount for each name
As for how to calculate the amounts, It is the same way... Thank you for your assistance and cooperation.

[Paul_Hossler]

I need an example

What does the '30' mean on some of the entries?

[jonsonbero]

'30' Means a percentage of the value of the output And it can be changed as needed to 50 or 60 or 70 According to the employee's work nature

[Paul_Hossler]

'30' Means a percentage of the value of the output And it can be changed as needed to 50 or 60 or 70 According to the employee's work nature

The macro in post#4 does that

If Col A has 'cut30' it uses 30%. If col A has 'part40' it uses 40%



Column B contains some names like one, two, three ... I want to give each name a specific percentage plus a specified amount

This is the part that I don not understand

[jonsonbero]

Yes sir, of course the macro in Post # 4 does it very very well
please follow your approach to coding .... For illustrative purposes only
Here's the same UDF but to multiply the column C value by the percentage specified for each name.
but I'm having a problem in Collect a specified amount for each name In addition to the percentage assigned to it.
As for how to calculate the amounts,It is the same way According to the nature of the work of each case
I have highlighted the specific amounts for each name From within the codes editor.
For more clarification,please refer my attach file... Thanks in advance.

[Paul_Hossler]

Was there a data error? Your G6 is different than your H16.

My UDF get the value in H16


27672




Option Explicit


Const Bonus As Double = 2000#


Function myUDF_2(Condition1 As String, Condition2 As String, TheVal As Double, EvacDate As Date) As Variant
Dim StartDate As Date
Dim Factor2 As Double
Dim Duration As Long, NumDaysInEvacMonth As Long
Dim Full As Double

myUDF_2 = vbNullString

StartDate = DateSerial(Year(Date), Month(Date), 1)
Duration = EvacDate - StartDate
NumDaysInEvacMonth = Day(DateSerial(Year(EvacDate), Month(EvacDate) + 1, 0))


Select Case LCase(Condition2)
Case "one"
Full = 4# * TheVal + Bonus
Case "two"
Full = 5# * TheVal + Bonus
Case "three"
Full = 6# * TheVal + Bonus
Case Else
Exit Function
End Select


If IsNumeric(Right(Condition1, 2)) Then
Factor2 = CDbl(Right(Condition1, 2)) / 100#
Else
Factor2 = 1#
End If


Select Case LCase(Condition1)
Case "on the job"
myUDF_2 = Full


Case "ending service"
myUDF_2 = Duration * Full / NumDaysInEvacMonth


Case "death"
myUDF_2 = (Duration + 1) * Full / NumDaysInEvacMonth


Case Else
If Condition1 Like "partdeath*" Then
myUDF_2 = Factor2 * (Duration + 1) * Full / NumDaysInEvacMonth

ElseIf Condition1 Like "partpension*" Then
myUDF_2 = Factor2 * Duration * Full / NumDaysInEvacMonth

ElseIf Condition1 Like "part*" Then
myUDF_2 = Factor2 * Full

ElseIf Condition1 Like "cut*" Then
myUDF_2 = Factor2 * (Duration + 1) * Full / NumDaysInEvacMonth + _
(NumDaysInEvacMonth - Duration - 1) * Full / NumDaysInEvacMonth
End If
End Select


End Function

[jonsonbero]

Thank you very very much Mr. Paul_Hossler for great help and thanks a lot for your patience ...You are great personality
An excellent test from you to me ... We can now distinguish between all cases as follows


Option Explicit
Const Bonus As Double = 2000#
Const Bonus1 As Double = 3000#
Const Bonus2 As Double = 4000#
Const Bonus3 As Double = 5000#
Const Bonus4 As Double = 6000#
Const Bonus5 As Double = 7000#

I will come back to you later for another assignment ...Thank you once again for the support and your patience ... Have a Great Day

[Paul_Hossler]

I will come back to you later for another assignment

1. 'Assignment' is probably not the best word since people are volunteers to help, and not a free coding service. I assume that you meant 'request'

2. I did not see when you're putting the Const lines or how you intend to use them

3. This is slightly better version since it's better documented and the code is cleaned up a bit




Option Explicit


Const Bonus As Double = 2000#


Function myUDF_2(Condition1 As String, Condition2 As String, TheVal As Double, EvacDate As Date) As Variant
Dim StartDate As Date
Dim FactorPercent As Double, FactorStartToBeforeEvac As Double, FactorStartToEvac As Double, FactorEvacToEnd As Double
Dim NumDaysBeforeEvac As Long, NumDaysInEvacMonth As Long
Dim Full As Double

myUDF_2 = vbNullString

Select Case LCase(Condition2)
Case "one"
Full = 4# * TheVal + Bonus
Case "two"
Full = 5# * TheVal + Bonus
Case "three"
Full = 6# * TheVal + Bonus
Case Else
Exit Function
End Select


If IsNumeric(Right(Condition1, 2)) Then
FactorPercent = CDbl(Right(Condition1, 2)) / 100#
Else
FactorPercent = 1#
End If


StartDate = DateSerial(Year(Date), Month(Date), 1)
NumDaysBeforeEvac = EvacDate - StartDate
NumDaysInEvacMonth = Day(DateSerial(Year(EvacDate), Month(EvacDate) + 1, 0))


FactorStartToBeforeEvac = NumDaysBeforeEvac / NumDaysInEvacMonth
FactorStartToEvac = (NumDaysBeforeEvac + 1) / NumDaysInEvacMonth
FactorEvacToEnd = (NumDaysInEvacMonth - NumDaysBeforeEvac - 1) / NumDaysInEvacMonth


Select Case LCase(Condition1)
Case "on the job"
myUDF_2 = Full


Case "ending service"
myUDF_2 = FactorStartToBeforeEvac * Full


Case "death"
myUDF_2 = FactorStartToEvac * Full


Case Else
If Condition1 Like "partdeath*" Then
myUDF_2 = FactorPercent * FactorStartToEvac * Full

ElseIf Condition1 Like "partpension*" Then
myUDF_2 = FactorPercent * FactorStartToBeforeEvac * Full

ElseIf Condition1 Like "part*" Then
myUDF_2 = FactorPercent * Full

ElseIf Condition1 Like "cut*" Then
myUDF_2 = (FactorPercent * FactorStartToEvac + FactorEvacToEnd) * Full
End If
End Select


End Function

[jonsonbero]

Thank you very very much Mr. Paul_Hossler for this perfect code
Yes Dear, Already I mean a request Other than that, no ... I am just amateur not a professional and My job is an auditor
So I appreciate the Helping the experts here ... Please accept my apology for an unintended mistake
Till we meet again in impossible request , I have to say goodbye With my best wishes of good and great luck for you forever.

[Paul_Hossler]

No problem

I'll be looking forward to seeing you here again

Take care and be safe

[jonsonbero]

Thank you very much Mr.Paul_Hossler for your interest
Column B contains some names like one, two, three .. But when changing these names to others in the same column I'm getting an error #VALUE.
The reason is Using rounding functions like ROUND OR INT OR TRUNC
How can adjust rounding value Using these functions By including them in the code
Thanks in advance

[Paul_Hossler]

I can't reproduce the error

Please post an example

[jonsonbero]

Here's the code from the previous issue.

Thanks again.

[Paul_Hossler]

Failing Condition1 used to return a null string (you didn't want a 0). Changed to return a 0. That was the problem I think

Incorporated CEILING into the UDF




Option Explicit


Const Bonus As Double = 1000#


Function myUDF(Condition1 As String, Condition2 As String, TheVal As Double, EvacDate As Date) As Variant
Dim StartDate As Date
Dim FactorPercent As Double, FactorStartToBeforeEvac As Double, FactorStartToEvac As Double, FactorEvacToEnd As Double
Dim NumDaysBeforeEvac As Long, NumDaysInEvacMonth As Long
Dim Full As Double, Temp As Double ' <<<<<<<<<<<<<<<<<<<<<<

myUDF = 0# ' <<<<<<<<<<<<<<<<<<<<<<

Select Case LCase(Condition2)
Case "one"
Full = 4# * TheVal + Bonus
Case "two"
Full = 5# * TheVal + Bonus
Case "three"
Full = 6# * TheVal + Bonus
Case Else

Exit Function
End Select

If IsNumeric(Right(Condition1, 2)) Then
FactorPercent = CDbl(Right(Condition1, 2)) / 100#
Else
FactorPercent = 1#
End If

StartDate = DateSerial(Year(Date), Month(Date), 1)
NumDaysBeforeEvac = EvacDate - StartDate
NumDaysInEvacMonth = Day(DateSerial(Year(EvacDate), Month(EvacDate) + 1, 0))
FactorStartToBeforeEvac = NumDaysBeforeEvac / NumDaysInEvacMonth
FactorStartToEvac = (NumDaysBeforeEvac + 1) / NumDaysInEvacMonth
FactorEvacToEnd = (NumDaysInEvacMonth - NumDaysBeforeEvac - 1) / NumDaysInEvacMonth

Select Case LCase(Condition1)
Case "on the job"
Temp = Full
Case "ending service"
Temp = FactorStartToBeforeEvac * Full
Case "death"
Temp = FactorStartToEvac * Full
Case Else
If Condition1 Like "partdeath*" Then
Temp = FactorPercent * FactorStartToEvac * Full
ElseIf Condition1 Like "partpension*" Then
Temp = FactorPercent * FactorStartToBeforeEvac * Full
ElseIf Condition1 Like "part*" Then
Temp = FactorPercent * Full
ElseIf Condition1 Like "cut*" Then
Temp = (FactorPercent * FactorStartToEvac + FactorEvacToEnd) * Full
End If
End Select

myUDF = Application.WorksheetFunction.Ceiling(Temp, 0.05) ' <<<<<<<<<<<<<<<<<<<


End Function

[jonsonbero]

That's great. Mr.Paul_Hossler
I will come back later


Many thanks and kind regards

[jonsonbero]

Thanks Mr. Paul_Hossler for your great support all the time.
The Following example is to give each name a specific amount like one, two, three
but I've added a new column contains some different names like Fourth, Fifth, Sixth
the question is How can put the results in one column
I have highlighted the specific amounts for each name From within the codes editor.
For more clarification,please refer my attach file... Thanks in advance.

[Paul_Hossler]

If I'm understanding, look at this part in MyUDF3 function





'************************************************************************** **
If Len(Trim(Condition3)) = 0 Then
Select Case LCase(Condition2)
Case "one"
Full = 0# * TheVal + Bonus
Case "two"
Full = 0# * TheVal + Bonus2
Case "three"
Full = 0# * TheVal + Bonus3
Case Else
Exit Function
End Select

ElseIf Len(Trim(Condition2)) = 0 Then
Select Case LCase(Condition3)
Case "fourth"
Full = 0# * TheVal + Bonus4
Case "fifth"
Full = 0# * TheVal + Bonus5
Case "sixth"
Full = 0# * TheVal + Bonus6
Case Else
Exit Function
End Select

Else
Exit Function
End If

[jonsonbero]

Yes, my dear friend ... You understand me wonderfully
Now I can add many conditions To achieve many of the conditions.
You made my day really happy ... There are two points ... I will come back to you later ... Have a nice time

[jonsonbero]

Thanks Mr. Paul_HosslerThere is a point that needs to be modified in the previous version of UDFplease refer attach file ...Thanks in advance.

[Paul_Hossler]

Look at the use of bCond2OK and bCond3OK

[jonsonbero]

Thank you very very much Paul_Hossler for this wonderful UDF and great solution.:clap::clap::clap::clap:
again, You made my day really happy ... There are two points ... I will come back to you tomorrow ... I wish you a great night

[jonsonbero]

Thanks Mr. Paul_Hossler
Please see the attachment file.
Thanks again. Much appreciated.

[Paul_Hossler]

Try this

Are you sure that your expected value for CUT50 is correct?

[jonsonbero]

Sorry my dear friend, you are correct ... the results are correct ... I will come back to you later ... Have a nice time

[jonsonbero]

Thanks Mr. Paul_Hossler
Please see the attached before the last
Thank you for your patience with me

[Paul_Hossler]

Please check your manual calculations for the items in red

27689

[jonsonbero]

Mr.Paul_Hossler, I'm sorry for taking you so much time... And, of course, thank you!
my manual calculations for the items in red Correct except for one number inverse
I have made some adjustments to (i hope) but no avail.... Please check again.
Please see the attachment file ...Thanking you in advance for your insight.

[Paul_Hossler]

Still not sure that many of your manual calculations are correct

Check col M and N which are my manual calculations. I just did 'Ending service' 'one' ok' case

The 'Temp' value is calculated the same way as all the other UDF functions have been

[jonsonbero]

Thanks Mr. Paul_Hossler
I will explain more about the formula used later to get the correct results
I lost my focus ... I'll come back to you later ...

Thanks a lot for your patience in this issue

[jonsonbero]

Thanks a lot Mr. Paul_Hossler
I have a question for you ... How can you convert this formula to UDF Function Without including it in within the code?

=IF(OR(A2="one";A2="two";A2="three");IF(B2="OK";ROUND(D2*5%+D2*5%*7%;2)+SUM(D2)+ROUND(MAX(MIN(7%*D2;7%*D2);75);2);SUM(D2)+ ROUND(MAX(MIN(7%*D2;7%*D2);75);2));"")
I wish you good luck, success and happiness