Nested If statement Syntax error in VBA when run after recording

Question

Excel 2013 Enterprise

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:

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:

"=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?

Answer 1

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 ;)

Answer 2

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:

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