IsNumeric/IsError returns an error

Question

I'm quite new to VBA and working on a custom function (which will be used for more complicated purposes in the future - that is the reason for which I want to do this using VBA instead of standard formulas) whose task should be to get the date from the cell and specify its position (i.a. row number) within the column (e.g. column A).

This simple task I have solved by the following procedure:

Function TempRowNumber(OrgDate)
    TempRowNumber = WorksheetFunction.Match(OrgDate, Range("A:A"), 0)
End Function

It works well, of course. However the problem is, that it may happen that the date entered into function is not included in the range. In this case, the function should search for the latest entered date, before that date. As in this range the dates are always sorted chronologically, I planned to construct an error checking formula (as an condition) and the appropriate loop.

The problem is, that if I write a formula like this:

=ISERROR(MATCH(C23;A:A;0))

it's all good (the formula returns TRUE if the date from cell C23 is found within the column A, and FALSE if the date is not found). But if try to rewrite this formula into VBA procedure, i.a.:

Function FindDate(OrgDate)

FindDate = IsError(WorksheetFunction.Match(OrgDate, Range("A:A"), 0))

End Function

the custom function returns #VALUE! error if the date is not found (and FALSE if it's not).
Below I attach a snapshot about what I describe.

Have you any idea what should I do? Thanks in advance.

But I actually use the IsError() function and it does return "#VALUE!" instead of TRUE value if the date which I search for is not found. However, the Iif() function only specify what to do in case of TRUE and FALSE value, whereas I have a problem with the condition (as decribed above, VBA procedure wrote by me results in FALSE value or "#VALUE!" error). — azera

L42 L42 · Accepted Answer · 2015-04-24T22:21:23

I don't know the reason behind it but this works:

Function FindDate(Orgdate)
    FindDate = IsError(Application.Match(Orgdate, Range("A:A"), 0))
End Function

Logically it should work the same way as WorksheetFunction.Match and can be evaluated by IsError the same way but I've tried it and I'm getting the same result as yours.

IsNumeric/IsError returns an error

1 Answers