How to adapt this macro to allow for missing values?

[billj]

Dear all,

The macro below works well. But if any cell contains #NA or missing values, it will fail to work. My question is: how to make this macro work to ignore all non-numeric values?

Function MaxDrawDown(returns As Variant) As Variant
Dim TS As Variant
Dim n As Integer
Dim Min As Double
TS = returns
n = UBound(TS)
Min = 0
For i = 1 To n
For j = i To n
temp = TS(j, 1) / TS(i, 1) - 1
If temp < Min Then
Min = temp
End If
Next
Next
MaxDrawDown = Min
End Function

[MickG]

Try this:-

Please Login or Register  to view this content.

[billj]

Thanks, Mick. It worked well

[MickG]

Thanks for the feedback
Rgrds Mick