jaydee
10-29-2012, 05:53 PM
Hi All,
I’m trying to create a user-defined function with Select Case, but I’m having problems getting it to work.
In the worksheet attached, column AF has items grouped into an age category which tells how long it’s been since the last product was sold.
The UDF I’m trying to create would return a certain percentage depending on the values in column AF, and some fields return “No Data”. If this occurs, I want to use an if statement. If today’s date (located in cell AG1) minus the date in column L is greater than 180, return 5%, if not return 0. A version of the original formula is in column AM (related cols are highlighted in blue)
I wrote the code below, but it’s not returning any of the values I want. Can someone help? Thank you
Function SlowMovingP(Age_Catagory)
Select Case Age_Catagory
Case "Current": SlowMovingP = 0
Case "05-06": SlowMovingP = 0.05
Case "07-09": SlowMovingP = 0.1
Case "10-12": SlowMovingP = 0.15
Case "13-16": SlowMovingP = 0.3
Case "17-20": SlowMovingP = 0.45
Case "21-24": SlowMovingP = 0.6
Case "24+": SlowMovingP = 0.7
Case "No Data"
If Range("AG1").Value - ActiveCell.FormulaR1C1 = "=RC[-21]" > 180 Then
SlowMovingP = 0.05
Else
SlowMovingP = 0
End If
End Select
I’m trying to create a user-defined function with Select Case, but I’m having problems getting it to work.
In the worksheet attached, column AF has items grouped into an age category which tells how long it’s been since the last product was sold.
The UDF I’m trying to create would return a certain percentage depending on the values in column AF, and some fields return “No Data”. If this occurs, I want to use an if statement. If today’s date (located in cell AG1) minus the date in column L is greater than 180, return 5%, if not return 0. A version of the original formula is in column AM (related cols are highlighted in blue)
I wrote the code below, but it’s not returning any of the values I want. Can someone help? Thank you
Function SlowMovingP(Age_Catagory)
Select Case Age_Catagory
Case "Current": SlowMovingP = 0
Case "05-06": SlowMovingP = 0.05
Case "07-09": SlowMovingP = 0.1
Case "10-12": SlowMovingP = 0.15
Case "13-16": SlowMovingP = 0.3
Case "17-20": SlowMovingP = 0.45
Case "21-24": SlowMovingP = 0.6
Case "24+": SlowMovingP = 0.7
Case "No Data"
If Range("AG1").Value - ActiveCell.FormulaR1C1 = "=RC[-21]" > 180 Then
SlowMovingP = 0.05
Else
SlowMovingP = 0
End If
End Select