iserror in VBA.

[G2K]

Hi,

i have been trying to handle the error in vba for last two days and have tried all the possible option i know but unfortunately not got rid of this error till now. below is the code-

if not iserror(Application.WorksheetFunction.Rank(Cells(a, 24), Range("x:x"))) then

Activecell.value = Application.WorksheetFunction.Rank(Cells(a, 24), Range("x:x"))

else

activecell.value = "NA"

end if

i just want rank value in Activecell and if there is any error or cell is blank in corosponding X column, function should return "NA" for this cell. but given code generates error - "Run time error 1004"

"Unable to get the rank property of the worksheetFunction class"

whenever it encounters a blank cell, otherwise it's working fine.

Please help.........

Thanks

 

[RyanS]

Try this:

    Err.Clear
    On Error Resume Next
    ActiveCell.Value = Application.WorksheetFunction.Rank(Cells(a, 24), Range("x:x"))
    If Err.Number > 0 Then
        ActiveCell.Value = "NA"
    End If
    On Error GoTo 0

Hope it helps
Ryan

 

[G2K]

Thanks Ryan,

It's working like a charm,

would you please explain why WorksheetFunction.IsError/Iserr or all other error hanling options could not work here.

and why do we need multiple error handler for one line code.

thanks agian for your kind help

 

[RyanS]

When "Application.WorksheetFunction.Rank" returns an error you get an immediate run time error and your code crashes. You have to avoid this crash
by using:
On Error Resume Next

To actually pick up that an error occurred you use Err.Number.

 

[RyanS]

An alternative approach would be as follows:

    On Error GoTo ErrOccurred
    ActiveCell.Value = Application.WorksheetFunction.Rank(Cells(a, 24), Range("x:x"))
    GoTo NoErr
ErrOccurred:
    ActiveCell.Value = "NA"
NoErr:
    rest of code