locked
User Defined Function Returning 0 need help please tight deadline RRS feed

  • Question

  • Hello, I have created a user defined function in Excel that is returning 0.  I cannot tell what is wrong with this function.  Any help is greatly appreciated!! :D

    Public Function CalcRange(Days, NotifDays)

    Dim result As Integer

    If NotifDays = 30 Then

    Select Case Days
        Case Is <= 30
            result = 1
        Case 31 To 60
            result = 2
        Case 61 To 90
            result = 3
        Case 91 To 120
            result = 4
        Case 121 To 150
            result = 5
        Case 151 To 180
            result = 6
        Case 181 To 210
            result = 7
        Case 211 To 240
            result = 8
        Case 241 To 270
            result = 9
        Case 271 To 300
            result = 10
        Case 301 To 330
            result = 11
        Case 331 To 360
            result = 12
        Case 361 To 390
            result = 13
        Case 391 To 420
            result = 14
        Case 421 To 450
            result = 15
        Case Else
            result = "error"
    End Select

    Else

    If NotifDays = 45 Then

    Select Case Days
        Case Is <= 45
            result = 1
        Case 46 To 90
            result = 2
        Case 91 To 135
            result = 3
        Case 136 To 180
            result = 4
        Case 181 To 225
            result = 5
        Case 226 To 270
            result = 6
        Case 271 To 315
            result = 7
        Case 316 To 360
            result = 8
        Case 361 To 405
            result = 9
        Case 406 To 450
            result = 10
        Case 451 To 495
            result = 11
        Case 496 To 540
            result = 12
        Case 541 To 585
            result = 13
        Case 586 To 630
            result = 14
        Case 631 To 675
            result = 15
        Case Else
            result = "error"
    End Select


    If NotifDays = 90 Then

    Select Case Days
        Case Is <= 90
            result = 1
        Case 91 To 180
            result = 2
        Case 181 To 270
            result = 3
        Case 271 To 360
            result = 4
        Case 361 To 450
            result = 5
        Case 451 To 540
            result = 6
        Case 541 To 630
            result = 7
        Case 631 To 720
            result = 8
        Case 721 To 810
            result = 9
        Case 811 To 900
            result = 10
        Case 901 To 990
            result = 11
        Case 991 To 1080
            result = 12
        Case 1081 To 1170
            result = 13
        Case 1171 To 1260
            result = 14
        Case 1261 To 1350
            result = 15
        Case Else
            result = "error"
    End Select
    End If
    End If
    End If

    End Function


    Robert

    Thursday, October 8, 2015 4:36 PM

All replies

  • Your function returns 0 since you don't assign a value to CalcRange.

    You can simplify the code to

    Public Function CalcRange(Days, NotifDays)
        CalcRange = (Days - 1) \ NotifDays + 1
    End Function


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Thursday, October 8, 2015 7:53 PM