Convert Multiple If statments to Case

[rumshar]

How do I write this UDF with the help of 'case' function?

Function AgingBucket(Age As Integer) As String

If Age >= 120 Then
AgingBucket = "120 Days"

ElseIf Age >= 90 Then
AgingBucket = "90 Days"

ElseIf Age >= 60 Then
AgingBucket = "60 Days"

ElseIf Age >= 30 Then
AgingBucket = "30 Days"

ElseIf Age < 30 Then
AgingBucket = "Current"

End If
End Function



With Regards
Rudra

 

[iferror]

Hi Rudra,
assuming that you want to stick to VBA and UDF, this is the SELECT CASE you're looking for

Function AgingBucket(Age As Integer) As String
Select Case Age
Case Is >= 120
  AgingBucket = "120 Days"
Case Is >= 90
  AgingBucket = "90 Days"
Case Is >= 60
  AgingBucket = "60 Days"
Case Is >= 30
  AgingBucket = "30 Days"
Case Else
  AgingBucket = "Current"
End Select

End Function

Another way to go would be a simple formula

=IF(INT(A1/30)=0;"Current";((INT(A1/30))*30) & " Days")

assuming that you have your Age in cell A1

 

[rumshar]

Hi Rudra,
assuming that you want to stick to VBA and UDF, this is the SELECT CASE you're looking for

Function AgingBucket(Age As Integer) As String
Select Case Age
Case Is >= 120
  AgingBucket = "120 Days"
Case Is >= 90
  AgingBucket = "90 Days"
Case Is >= 60
  AgingBucket = "60 Days"
Case Is >= 30
  AgingBucket = "30 Days"
Case Else
  AgingBucket = "Current"
End Select

End Function

Another way to go would be a simple formula

=IF(INT(A1/30)=0;"Current";((INT(A1/30))*30) & " Days")

assuming that you have your Age in cell A1

Thanks a lot friend..
Rudra