Find #Value Error

[chg]

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

 

[Ron Rosenfeld]

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

 

[chg]

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

 

[Ron Rosenfeld]

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.