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

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

So simple, damn I'm stupid.
Your my personal hero for today. Thanks!

On Tue, 26 Apr 2011 03:40:17 -0700 (PDT), chg wrote:

So simple, damn I'm stupid.
Your my personal hero for today. Thanks!

Glad to help. Thanks for the feedback. Most things seem simple AFTER you figure them out, or have it pointed out. But until then, many things seem pretty complex.