将一个Evaluate语句的输出传递给另一个.获取错误2015

Question

我能够让第一个Evaluate函数工作。但不是第二个。它在strBulkBP的第二次评估中给出了"错误2015"。第一个函数的输出被传递给第二个函数。请帮助修复代码。谢谢

Sub test1()
Dim strBulkNum as Variant
Dim strBulkBP As Variant
Set wksh3 = Workbooks("Master - Data.xlsx").Sheets("Data")
Set wksh4 = Workbooks("Warranty_Analysis.xlsm").Sheets("TTX-OWNER_data")
Set rngBulkNum = wksh3.Range("BulkNum") 'column A:A
Set rngRefNum = wksh3.Range("RefNum") 'column N:N
Set rngBPNum = wksh4.Range("BPNum") 'column A:A
Set rngBPBulkNum = wksh4.Range("BPBulkNum") 'column E:E
strRefNum = "ES80381"
' The code below works to give a value for strBulkNum = "MX12049"
strBulkNum = Evaluate("=IFERROR(INDEX('[Master - Data.xlsx]Data'!" & rngBulkNum.Address(False, True) & ", SMALL(IF('[Master - Data.xlsx]Data'!" & rngRefNum.Address(False, True) & "=""" & strRefNum & """, ROW('[Master - Data.xlsx]Data'!" & rngRefNum.Address(False, True) & "))," & 2 & ")),"""")")
' The code below does not work. Gives "Error 2015" for strBulkBP
strBulkBP = Evaluate("=IFERROR(INDEX('[Warranty_Analysis.xlsm]TTX-OWNER_data'!" & rngBPNum.Address(False, True) & ", SMALL(IF('[Warranty_Analysis.xlsm]TTX-OWNER_data'!" & rngBPBulkNum.Address(False, True) & "=""" & strBulkNum & """,ROW('[Warranty_Analysis.xlsm]TTX-OWNER_data'!" & rngBPBulkNum.Address(False, True) & "))," & 2 & ")),"""")")
'The below code works - using helper cells & then populating the variable
Set wkshCal = ThisWorkbook.Sheets("Calculation")
Dim FormulaPart1 As String 
Dim FormulaPart2 As String 
wkshCal.Range("A1") = strBulkNum
FormulaPart1 = "=IFERROR(INDEX('[Warranty_Analysis.xlsm]TTX-OWNER_data'!$A:$A, xxx()),"""")" 
FormulaPart2 = "SMALL(IF('[Warranty_Analysis.xlsm]TTX-OWNER_data'!$E:$E = $A$1, ROW('[Warranty_Analysis.xlsm]TTX-OWNER_data'!$E:$E)), 1)" 
With wkshCal.Range("B1") 
.FormulaArray = FormulaPart1 
.Replace "xxx()", FormulaPart2 
End With 
strBulkBP = Range("B1") 
End Sub

Answer 1

'下面的代码有效-使用辅助单元格&然后填充变量。然而，数组公式需要分为两部分，以使其每个少于255个字符。一个更好的解决方案可以是变量直接存储计算。Set wkshCal=ThisWorkbook.Sheets("计算"(将FormulaPart1标注为字符串Dim FormulaPart2作为字符串

wkshCal.Range("A1") = strBulkNum
FormulaPart1 = "=IFERROR(INDEX('[Warranty_Analysis.xlsm]TTX-OWNER_data'!$A:$A, xxx()),"""")" 
FormulaPart2 = "SMALL(IF('[Warranty_Analysis.xlsm]TTX-OWNER_data'!$E:$E = $A$1, ROW('[Warranty_Analysis.xlsm]TTX-OWNER_data'!$E:$E)), 1)" 
With wkshCal.Range("B1") 
.FormulaArray = FormulaPart1 
.Replace "xxx()", FormulaPart2 
End With 
strBulkBP = Range("B1")