Пользовательская формула Excel с инструкциями IF и INDEX, MATCH возвращает ошибку #VALUE

Question

Я извлекаю значения из одного листа Excel в другой. Я использую следующую формулу:

= IF(ISBLANK(IFNA(INDEX(Range1;Row;MATCH(Dates;Range2;0));Error1));Error2;IFNA(INDEX(Range1;Row;MATCH(Dates;Range2;0));Error1))

Поскольку мне нужно выполнять вычисления с извлеченными данными, я хотел создать пользовательскую функцию, которая упростила бы процесс извлечения, устраняя необходимость вводить параметры, которые всегда остаются неизменными. Поэтому я построил следующую функцию:

Function DataCap(Dates As Variant, Row As Variant) As Variant

   Dim Range1 As Range 'Define the whole lookup table
       Set Range1 = Range("Data!A1:P151")

   Dim Range2 As Range 'Define the date lookup table
       Set Range2 = Range("Data!A3:P3")

   Dim Error1 As Range 'Define the error 1; if no value
       Set Error1 = Range("X16")

   Dim Error2 As Range 'Define the error 2; if blank
       Set Error2 = Range("Y16")

   DataCap.Formula "= IF(ISBLANK(IFNA(INDEX(Range1;Row;MATCH(Dates;Range2;0));Error1));Error2;IFNA(INDEX(Range1;Row;MATCH(Dates;Range2;0));Error1))"


End Function

Когда я запускаю его, я получаю ошибку #VALUE. Если я вставлю уравнение (с теми же аргументами) прямо в ячейку, оно будет работать как положено. Итак, я думаю, что проблема в том, что VBA не может сделать расчет. Любые предложения, как это исправить?

Спасибо за помощь заранее

Answer 1

Не имея возможности проверить это на ваших данных, я не могу быть уверен, что это сработает, но, по крайней мере, оно могло бы указать вам больше в правильном направлении.

Public Function DCap(xDates As Variant, xRow As Variant)
Dim Range1 As Range, Range2 As Range, Error1 As Range, Error2 As Range

Set Range1 = ActiveWorkbook.Sheets("Data").Range("A1:P151")
Set Range2 = ActiveWorkbook.Sheets("Data").Range("A3:P3")

Set Error1 = ActiveWorkbook.ActiveSheet.Range("X16")
Set Error2 = ActiveWorkbook.ActiveSheet.Range("Y16")

On Error Resume Next
If IsError(WorksheetFunction.Index(Range1, xRow, WorksheetFunction.Match(xDates, Range2, 0))) Then
    DCap = Error1
ElseIf Len(WorksheetFunction.Index(Range1, xRow, WorksheetFunction.Match(xDates, Range2, 0))) = 0 Then
    DCap = Error2
Else
    DCap = WorksheetFunction.Index(Range1, xRow, WorksheetFunction.Match(xDates, Range2, 0))
End If
On Error GoTo 0

End Function