Max Number of Arguments within an Excel Function

[jallenamdg]

OK....I'm trying to add an additional block to find the value on another sheet (with the named references). I've formatted it for easier comparison but in actuality this formula is set within a VBA module with a simple assignment statement.

Here's the Original - Obviously, it works fine as is and works if I substitute 4 for 3 on the "..CCR_X" reference (Ex. WO_KeyCCR_4)
=IF(
RC4="","",
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC4,WO_KeyCCR_1,0),MATCH("WO Qty",WO_HdrCCR_1,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC4,WO_KeyCCR_2,0),MATCH("WO Qty",WO_HdrCCR_2,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC4,WO_KeyCCR_3,0),MATCH("WO Qty",WO_HdrCCR_3,0)),
"Not in Source Data"))))


New - Application Error during assignment statement (R/T Error 1004 - Application-defined or object-defined error)
=IF(
RC4="","",
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC4,WO_KeyCCR_1,0),MATCH("WO Qty",WO_HdrCCR_1,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC4,WO_KeyCCR_2,0),MATCH("WO Qty",WO_HdrCCR_2,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC4,WO_KeyCCR_3,0),MATCH("WO Qty",WO_HdrCCR_3,0)),
IF(NOT(ISERROR(MATCH(RC4,WO_KeyCCR_4,0))),INDEX(WO_TblCCR_4,MATCH(RC4,WO_KeyCCR_4,0),MATCH("WO Qty",WO_HdrCCR_4,0)),
"Not in Source Data")))))

While the number of IF blocks in the new one doesn't exceed 7, I'm wondering if the additional block is exceeding some other limit on complexity?

Thanks!

 

[jallenamdg]

FYI - I've already experimented with the number of parentheses at the end to no avail....

 

[Fluff]

If you are getting an RTE then we need to see the code.

 

[jallenamdg]

Range(Cells(2, coltrgt1).Address & ":" & Cells(RowLast, coltrgt1).Address).FormulaR1C1 = _
        "=IF(RC" & coltrgt2 & "="""","""",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_1,0),MATCH(""WO Qty"",WO_HdrCCR_1,0)),IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_2,0),MATCH(""WO Qty"",WO_HdrCCR_2,0)),IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_3,0),MATCH(""WO Qty"",WO_HdrCCR_3,0))),IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_4,0))),INDEX(WO_TblCCR_4,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_4,0),MATCH(""WO Qty"",WO_HdrCCR_4,0)),""Not in Source Data"")))))"

 

[Fluff]

Thanks for that, you've got an extra bracket in there, try

Range(Cells(2, coltrgt1), Cells(RowLast, coltrgt1)).FormulaR1C1 = _
        "=IF(RC" & coltrgt2 & "="""","""",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_1,0))),INDEX(WO_TblCCR_1,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_1,0),MATCH(""WO Qty"",WO_HdrCCR_1,0))" & _
        ",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_2,0))),INDEX(WO_TblCCR_2,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_2,0),MATCH(""WO Qty"",WO_HdrCCR_2,0))" & _
        ",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_3,0))),INDEX(WO_TblCCR_3,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_3,0),MATCH(""WO Qty"",WO_HdrCCR_3,0))" & _
        ",IF(NOT(ISERROR(MATCH(RC" & coltrgt2 & ",WO_KeyCCR_4,0))),INDEX(WO_TblCCR_4,MATCH(RC" & coltrgt2 & ",WO_KeyCCR_4,0),MATCH(""WO Qty"",WO_HdrCCR_4,0)),""Not in Source Data"")))))"