Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find #Value Error
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find #Value Error
So simple, damn I'm stupid.
Your my personal hero for today. Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find #Value Error
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error dialog box meaning and how to find error | Excel Worksheet Functions | |||
Find error!!!! Is there any other way to do this? | Excel Programming | |||
help with this error-Compile error: cant find project or library | Excel Discussion (Misc queries) | |||
change error message when no more for "find" in macro to find | Excel Programming | |||
Can someone find this error? | Excel Programming |