Implementing User-Defined Function (UDF) with IF equations

Question

I'm working on a function that uses different formulas based on the DOH of a specific Person in different groups. Some groups may use different formulas depending on the status (if it's active or non-active).

I need the results to be outputted in the Grace Period column, but the grace period column should only have the results for that specific group, and use that group's specific formula.

some of the groups use more than one formula depending on status, and the total number of groups change per day.

The current UDF works with simple addition and subtraction but some groups require more to be calculated such as the day, month, year.

I tried running some stacked if statements but kept getting erros, when trying to implement them into what I currently have.

I can't really use a pivot table since I specifically only need to have the information in the worksheet. Any help is appreciated, thank you.

Image of Worksheet

Function GracePeriod(grp, Status, DOH)
Application.Volatile True

    Select Case grp
    
        Case "Group 1": GracePeriod = DOH + 30
        Case "Group 2": GracePeriod = DOH + 60
        Case "Group 3": GracePeriod = DOH + 29
        Case "Group 4": GracePeriod = DOH + 30
        Case "Group 5": GracePeriod = DOH + 30
        Case "Group 6": GracePeriod = DOH + 30
        Case "Group 7": GracePeriod = DOH + 90
        Case "Group 9": GracePeriod = DOH + 30
        Case "Group 10": GracePeriod = DOH + 30
        Case "Group 11": GracePeriod = DOH + 60
        Case "Group 12": GracePeriod = DOH + 30
        Case "Group 13": GracePeriod = DOH + 60
        Case "Group 14": GracePeriod = DOH + 30
        Case "Group 15": GracePeriod = DOH + 30
        Case "Group 16": GracePeriod = DOH + 30
        Case "Group 17": GracePeriod = DOH + 30
        Case "Group 18": GracePeriod = DOH + 30
        Case "Group 19": GracePeriod = DOH + 59
        Case "Group 20": GracePeriod = DOH + 30
        Case "Group 21": GracePeriod = DOH + 30
        Case "Group 22": GracePeriod = DOH + 30
    
    'Select Case grp
    
        'Case "Group 23": GracePeriod = DATE(YEAR(DOH+60),MONTH(DOH+60)+1,1)
        'Case "Group 24": GracePeriod = IF(DAY(DOH+30)=1,DOH+30,EOMONTH(DOH+30,0)+1)
        'Case "Group 25": GracePeriod = (DATE(YEAR(DOH),MONTH(DOH)+1,0))
        'Case "Group 26": GracePeriod = IF(DAY(DOH+60)=1,DOH+60,EOMONTH(DOH+60,0)+1)-(30)
        'Case "Group 27": GracePeriod = DATE(YEAR(DOH+30),MONTH(DOH+30)+1,0)"
        'Case "Group 28": GracePeriod = (DATE(YEAR(DOH),MONTH(DOH)+1,0))"
        'Case "Group 29": GracePeriod = (DATE(YEAR(DOH),MONTH(DOH)+1,0))"
        'Case "Group 30": GracePeriod = (DATE(YEAR(DOH),MONTH(DOH)+1,0))"
        
        'Case "Group 31"
            'Select Case Status
                'Case "Active":GracePeriod = DATE(YEAR(DOH+30),MONTH(DOH+30)+1,-45)"
                'Case "Non Active": GracePeriod ="Pending"
            'End Select
        'End Select
        
    'Select case grp
        
        'Case "Group 32": GracePeriod= IF(DAY(DOH+60)=1,DOH+60,EOMONTH(DOH+60,0)+1)
        'Case "Group 33": GracePeriod= (DAY(DOH+60)=1,DOH+60,EOMONTH(DOH+60,0)+1)
        'Case "Group 34": GracePeriod=IF(DAY(DOH+60)=1,DOH+60,EOMONTH(DOH+60,0)+1)
        
        'Case "Group 35"
            'Select Case Status
                'Case "Active 1": GracePeriod = (DATE(YEAR(DOH),MONTH(DOH)+1,0))
                'Case "Active 2": GracePeriod = DOH-30
            'End Select
        'End Select
        
    'Select case grp
        
        'Case "Group 36"
            'Select case status
                'Case "Regular": GracePeriod = (DOH+90)-(1)
                'Case "Non Regular": GracePeriod = (DATE(YEAR(DOH+90),MONTH(DOH+90)+1,1))-(1)
            'End Select
        'End Select
        
    'Select case grp
    
        'Case "Group 37"
            'Select case status
                'Case "Active": GracePeriod = DATE(YEAR(DOH+60),MONTH(DOH+60)+1,-7*2)"
                'Case "Non Active": GracePeriod = (DATE(YEAR(DOH),MONTH(DOH)+1,-7*2))
            'End Select
        'End Select
    
    'Select case grp
    
        'Case "Group 38"
            'Select case status
                'Case "1", "2": GracePeriod = DATE(YEAR(DOH+60),MONTH(DOH+60)+1,-7*2)
                'Case "3","4": GracePeriod = (DATE(YEAR(DOH),MONTH(DOH)+1,-7*2))
            'End Select
        'End Select
         
End Function

taller_ExcelHome · Answer 1 · 2023-08-04T18:13:41.630

0

You can combine If statements with a Select Case as below.

Date(year, month, day) is a worksheet function. You can use DateSerial in vba.

    Select Case grp
        ' case ...
        Case "Group 24"
            If Day(DOH + 30) = 1 Then
                GracePeriod = DOH + 30
            Else
                GracePeriod = DateSerial(Year(DOH + 30), Month(DOH + 30) + 1, 1)
            End If
        Case "Group 31"
            If Status = "Active" Then
                GracePeriod = DateSerial(Year(DOH + 30), Month(DOH + 30) + 1, -45)
            ElseIf Status = "Non Active" Then
                GracePeriod = "Pending"
            End If
            ' case ...
    End Select

edited Aug 04 '23 at 18:13

answered Aug 04 '23 at 17:48

taller_ExcelHome

2,232
1
2
12

Worked perfectly, thank you! What about in the "Group 24" instance? Where it's "IF(DAY(DOH+30)=1,DOH+30,EOMONTH(DOH+30,0)+1)" the "=" seems to disrupt the case statement. – alxn Aug 04 '23 at 18:04
`IF(DAY...+1)` is a worksheet formula. You can get the same result with VBA `If`. I have updated the code. – taller_ExcelHome Aug 04 '23 at 18:16
Ahhhh okay, thank you. I was just overthinking everything!! Really appreciate your help. – alxn Aug 04 '23 at 18:28

Implementing User-Defined Function (UDF) with IF equations

1 Answers1