UDF and Select Case

[jaydee]

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

[vba]
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
[/vba]

[Teeroy]

The ActiveCell keeps changing and referencing it in your function will give you strange results. Pass the date to the function instead (eg =SlowMovingP(AF7,L7)) and change the function slightly to:

[vba]Function SlowMovingP(Age_Catagory, dDate)
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 - dDate > 180 Then
SlowMovingP = 0.05
Else
SlowMovingP = 0
End If
End Select
End Function[/vba]

[Bob Phillips]

You should also pass AG1 as a cell reference, or if it is always todays date you could just use the VBA Date. The UDF should have no hard-coding.

[jaydee]

Thanks guys, I really appreciate the help. I tried it out on the raw data, works great.

The formula is always using today's date, so I changed that part to the vba date formula.