VBA Function Not Working

[animas]

Public Function Test(iRange As Range)

Dim tmp As Integer

If Application.IsNumber(Range(iRange).Value) And Range(iRange).Value > 0 Then
    tmp = Range(iRange).Value
Else
    tmp = 100
End If

Test = tmp

End Function

I am passing a cell as argument and checking if the cell value is number and >0. But above function showing me value error. What did I do wrong here?

 

[mikerickson]

iRange is a range object, so Range(iRange) is causing an error. Try

Public Function Test(iRange As Range)

    Dim tmp As Integer

    Set iRange = iRange.Cells(1,1)

    If Application.IsNumber(iRange.Value) And (Val(CStr(iRange.Value > 0))) Then
        tmp = Range(iRange).Value
    Else
        tmp = 100
    End If

    Test = tmp

End Function

The Val(CStr(iRange.Value)) is there to prevent an error if iRange contains an error value, like #DIV/0.

 

[hiker95]

animas,

''Try changing this:
Public Function Test(iRange As Range)

''to this:
Public Function Test(iRange As Range)As Integer

 

[animas]

thanks.