记录后运行时，VBA中嵌套If语句语法错误

Excel 2013 Enterprise

Excel 2013企业

In my worksheet I have a nested If statement that I would like to have in a macro. Problem is that when I record it, VBA screws it up and makes it unusable (see code below). I use a function for the MySht because the sheet names are to be determined (a macro changes them after a txt file is uploaded.). MySht function:

在我的工作表中，我有一个嵌套的If语句，我希望在宏中有这样的语句。问题是，当我记录它时，VBA把它搞砸了，使它无法使用(参见下面的代码)。我使用MySht的函数，因为要确定表名(在上传txt文件后，宏将更改它们)。MySht功能:

Function MySht(ByVal sht As Integer)
  Application.Volatile
  MySht = Sheets(sht).Name
End Function

From:

来自:

=IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(1) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(1) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(1) & "'!C:C")=B3)*(INDIRECT("'" & MySht(1) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(1) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(2) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(2) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(2) & "'!C:C")=B3)*(INDIRECT("'" & MySht(2) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(2) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(3) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(3) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(3) & "'!C:C")=B3)*(INDIRECT("'" & MySht(3) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(3) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(4) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(4) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(4) & "'!C:C")=B3)*(INDIRECT("'" & MySht(4) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(4) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(5) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(5) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(5) & "'!C:C")=B3)*(INDIRECT("'" & MySht(5) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(5) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(6) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(6) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(6) & "'!C:C")=B3)*(INDIRECT("'" & MySht(6) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(6) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(7) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(7) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(7) & "'!C:C")=B3)*(INDIRECT("'" & MySht(7) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(7) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(8) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(8) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(8) & "'!C:C")=B3)*(INDIRECT("'" & MySht(8) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(8) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(9) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(9) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(9) & "'!C:C")=B3)*(INDIRECT("'" & MySht(9) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(9) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(10) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(10) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(10) & "'!C:C")=B3)*(INDIRECT("'" & MySht(10) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(10) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(11) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(11) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(11) & "'!C:C")=B3)*(INDIRECT("'" & MySht(11) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(11) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(12) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(12) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(12) & "'!C:C")=B3)*(INDIRECT("'" & MySht(12) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(12) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(13) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(13) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(13) & "'!C:C")=B3)*(INDIRECT("'" & MySht(13) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(13) & "'!C:C"))-ROW($A$2)+1)));
IF(NOT(ISERROR(MATCH(B3;INDIRECT("'" & MySht(14) & "'!C:C");0))); INDEX(INDIRECT("'" & MySht(14) & "'!J:J");SUMPRODUCT((INDIRECT("'" & MySht(14) & "'!C:C")=B3)*(INDIRECT("'" & MySht(14) & "'!D:D")=C3)*(ROW(INDIRECT("'" & MySht(14) & "'!C:C"))-ROW($A$2)+1))); "nope"))))))))))))))

To:

"=IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(1) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(1) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(1) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(1) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(1) & ""'!C:C""))-ROW(R2C1)+1)))," & Chr(10) & "IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(2) & ""'!C:C""),0)" & _
"X(INDIRECT(""'"" & MySht(2) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(2) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(2) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(2) & ""'!C:C""))-ROW(R2C1)+1)))," & Chr(10) & "IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(3) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(3) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MyS" & _
"" '!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(3) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(3) & ""'!C:C""))-ROW(R2C1)+1))),
"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(4) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(4) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(4) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(4) & ""'!D:D"")=R[1]C[-1])*("& _
"ECT(""'"" & MySht(4) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(5) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(5) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(5) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(5) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(5) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R"& _
"INDIRECT(""'"" & MySht(6) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(6) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(6) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(6) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(6) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(7) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht"& _
"!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(7) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(7) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(7) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(8) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(8) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(8) & ""'!C:C"")=R[1]C[-2])*(IN"& _
"'"" & MySht(8) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(8) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(9) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(9) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(9) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(9) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(9) & ""& _
")-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(10) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(10) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(10) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(10) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(10) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & My"& _
" ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(11) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(11) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(11) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(11) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(12) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(12) & ""'!E:E""),SUMP"& _
"NDIRECT(""'"" & MySht(12) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(12) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(12) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(13) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(13) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(13) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & "& _
" & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(13) & ""'!C:C""))-ROW(R2C1)+1))),"&chr(10)&"IF(NOT(ISERROR(MATCH(R[1]C[-2],INDIRECT(""'"" & MySht(14) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(14) & ""'!E:E""),SUMPRODUCT((INDIRECT(""'"" & MySht(14) & ""'!C:C"")=R[1]C[-2])*(INDIRECT(""'"" & MySht(14) & ""'!D:D"")=R[1]C[-1])*(ROW(INDIRECT(""'"" & MySht(14) & ""'!C:C"""& _
"C1)+1))), ""nope""))))))))))))))"

I tried to alter my original formula to match something VBA would like but no success:

我试图改变我原来的公式来匹配VBA想要但没有成功的东西:

"=IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(1) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(1) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(1) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(1) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(1) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(2) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(2) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(2) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(2) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(2) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(3) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(3) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(3) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(3) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(3) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(4) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(4) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(4) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(4) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(4) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(5) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(5) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(5) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(5) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(5) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(6) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(6) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(6) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(6) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(6) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(7) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(7) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(7) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(7) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(7) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(8) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(8) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(8) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(8) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(8) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(9) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(9) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(9) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(9) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(9) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(10) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(10) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(10) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(10) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(10) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(11) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(11) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(11) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(11) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(11) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(12) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(12) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(12) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(12) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(12) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(13) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(13) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(13) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(13) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(13) & ""'!C:C""))-ROW($A$2)+1)))," & _
"IF(NOT(ISERROR(MATCH(B2,INDIRECT(""'"" & MySht(14) & ""'!C:C""),0))), INDEX(INDIRECT(""'"" & MySht(14) & ""'!J:J""),SUMPRODUCT((INDIRECT(""'"" & MySht(14) & ""'!C:C"")=B2)*(INDIRECT(""'"" & MySht(14) & ""'!D:D"")=C2)*(ROW(INDIRECT(""'"" & MySht(14) & ""'!C:C""))-ROW($A$2)+1))), ""nope"" ))))))))))))))"

What can I change so Excel and VBA will accept my formula?

Excel和VBA会接受我的公式吗?

2 个解决方案

#1

Here is a partial answer to help you structure your code:

以下是帮助您构建代码的部分答案:

First write a function that checks the condition for you (a function makes sense because the condition is the same for each sheet.

首先编写一个为您检查条件的函数(一个函数是有意义的，因为每个表的条件是相同的。

Private Function checkCondition(sht As Worksheet) As Boolean
    'check the condition here
End Function

Then call that function for each sheet. To avoid so many nested If statements, you can put them inside a loop:

然后为每个表调用该函数。为了避免如此多的嵌套If语句，您可以将它们放入一个循环中:

Dim conditionMet As Boolean 'this is only so it stops checking the rest of the sheets once it finds one that doesn't fulfill the condition
Dim counter As Long

counter = 1
conditionMet = True

While counter <= 14 And conditionMet
    'it will exit the loop if the condition is not met for a sheet
    conditionMet = checkCondition(Sheet(counter))
Wend

If conditionMet Then
    'now do your stuff
Else
    'or something different
End If

#2

If I got you right, then you want a function like this:

如果我猜对了，那么你想要一个这样的函数:

Public Function ultraMatch(find_what_1 As String, in_column_1 As String, find_what_2 As Variant, in_column_2 As String, first_sheet As Long, last_sheet As Long, return_column As String) As String
Dim i As Long, j As Long, rng1_val, rng2_val
For i = first_sheet To last_sheet
  With Sheets(i)
    rng1_val = Intersect(.Columns(in_column_1), .UsedRange).Value
    If IsNumeric(Application.Match(find_what_1, rng1_val, 0)) Then
      rng2_val = Intersect(.Columns(in_column_2), .UsedRange).Value
      For j = Application.Match(find_what_1, rng1_val, 0) To UBound(rng1_val)
        If rng1_val(j, 1) = find_what_1 And rng2_val(j, 1) = find_what_2 Then
          ultraMatch = .Cells(j, return_column).Value
        End If
      Next
    End If
  End With
Next
End Function

And in your worksheet simply use something like this:

在你的工作表中，简单地使用如下内容:

=ultraMatch(B2,"C",D2,"D",1,14,"J")

or alternatively (less obvious to my eye):

或者(在我看来不太明显):

=ultraMatch(B2,3,D2,4,1,14,10)

This way, the whole function also is not volatile.

这样，整个函数也不会不稳定。

If you have any questions, just ask ;)

如果你有什么问题，尽管问;

#1