Find #Value Error
On Tue, 26 Apr 2011 02:41:56 -0700 (PDT), chg wrote:
I'm trying to implement a simple search algorithm via vba.
My Problem is that i cannot seem to handle a #value error within vba
The UDF is called "FindBP"
Sometimes the Variable vIndex causes an #N/A Error, resulting in an
#vlaue error of the function.
I want to replace the vIndex calculation, every time it gives an
error.
My Implementation doesn't seem to work:
.....
vIndex = WorksheetFunction.Match(vBP, rg, -1)
vCount = WorksheetFunction.Index(rg, vIndex)
If IsError(vCount) Then
vIndex = WorksheetFunction.Match(vBP, rg, 1)
FindBP = WorksheetFunction.Index(rg, vIndex)
Else
FindBP = WorksheetFunction.Index(rg, vIndex)
End If
....
Any help is highly appreciated.
Thy in advance
You need to trap the error, and then handle it.
Take a look at help for On Error.
So something like:
....
On Error GoTo ErrorHandler
vIndex = ...
vCount = ...
On Error GoTo 0
.... <more of your code
Exit Sub
ErrorHandler:
... Error handling code
...
On error resume next
End Sub
|