Passing output of one Evaluate statement to another. Getting Error 2015
up vote
-1
down vote
favorite
I'm able to get the first Evaluate function to work. But not the second one. It gives "Error 2015" in the second Evaluate for strBulkBP. Output of the first is being passed to the second function. Please help to fix the code.
Thanks.
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
excel vba evaluate
asked Nov 19 at 13:44
S. Shaw
65
add a comment |
up vote
-1
down vote
favorite
I'm able to get the first Evaluate function to work. But not the second one. It gives "Error 2015" in the second Evaluate for strBulkBP. Output of the first is being passed to the second function. Please help to fix the code.
Thanks.
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
excel vba evaluate
asked Nov 19 at 13:44
S. Shaw
65
Is there a reason you're usingEvaluatefor this instead of the equivalent VBA?
– Comintern
Nov 19 at 13:50
I need to store the value in a variable and use it after. Hence trying to use Evaluate method.
– S. Shaw
Nov 19 at 13:52
Do let me know the equivalent VBA code that would work. Thanks.
– S. Shaw
Nov 19 at 13:54
I just found out that I need to use Array function as the number of characters was exceeding 255. Even in Array function I needed to break the formula into two parts:
– S. Shaw
Nov 19 at 19:52
Dim FormulaPart1 As String Dim FormulaPart2 As String 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 Range("B1") .FormulaArray = FormulaPart1 .Replace "xxx()", FormulaPart2 End With strBulkBP = Range("B1")
– S. Shaw
Nov 19 at 19:55
add a comment |
up vote
-1
down vote
favorite
up vote
-1
down vote
favorite
I'm able to get the first Evaluate function to work. But not the second one. It gives "Error 2015" in the second Evaluate for strBulkBP. Output of the first is being passed to the second function. Please help to fix the code.
Thanks.
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
excel vba evaluate
asked Nov 19 at 13:44
S. Shaw
65
I'm able to get the first Evaluate function to work. But not the second one. It gives "Error 2015" in the second Evaluate for strBulkBP. Output of the first is being passed to the second function. Please help to fix the code.
Thanks.
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
excel vba evaluate
excel vba evaluate
asked Nov 19 at 13:44
S. Shaw
65
asked Nov 19 at 13:44
S. Shaw
65
edited Nov 19 at 20:04
asked Nov 19 at 13:44
S. Shaw
65
asked Nov 19 at 13:44
S. Shaw
65
asked Nov 19 at 13:44
S. Shaw
65
65
Is there a reason you're usingEvaluatefor this instead of the equivalent VBA?
– Comintern
Nov 19 at 13:50
I need to store the value in a variable and use it after. Hence trying to use Evaluate method.
– S. Shaw
Nov 19 at 13:52
Do let me know the equivalent VBA code that would work. Thanks.
– S. Shaw
Nov 19 at 13:54
I just found out that I need to use Array function as the number of characters was exceeding 255. Even in Array function I needed to break the formula into two parts:
– S. Shaw
Nov 19 at 19:52
Dim FormulaPart1 As String Dim FormulaPart2 As String 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 Range("B1") .FormulaArray = FormulaPart1 .Replace "xxx()", FormulaPart2 End With strBulkBP = Range("B1")
– S. Shaw
Nov 19 at 19:55
add a comment |
Is there a reason you're usingEvaluatefor this instead of the equivalent VBA?
– Comintern
Nov 19 at 13:50
I need to store the value in a variable and use it after. Hence trying to use Evaluate method.
– S. Shaw
Nov 19 at 13:52
Do let me know the equivalent VBA code that would work. Thanks.
– S. Shaw
Nov 19 at 13:54
I just found out that I need to use Array function as the number of characters was exceeding 255. Even in Array function I needed to break the formula into two parts:
– S. Shaw
Nov 19 at 19:52
Dim FormulaPart1 As String Dim FormulaPart2 As String 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 Range("B1") .FormulaArray = FormulaPart1 .Replace "xxx()", FormulaPart2 End With strBulkBP = Range("B1")
– S. Shaw
Nov 19 at 19:55
Is there a reason you're using
Evaluate for this instead of the equivalent VBA?– Comintern
Nov 19 at 13:50
Is there a reason you're using
Evaluate for this instead of the equivalent VBA?– Comintern
Nov 19 at 13:50
I need to store the value in a variable and use it after. Hence trying to use Evaluate method.
– S. Shaw
Nov 19 at 13:52
I need to store the value in a variable and use it after. Hence trying to use Evaluate method.
– S. Shaw
Nov 19 at 13:52
Do let me know the equivalent VBA code that would work. Thanks.
– S. Shaw
Nov 19 at 13:54
Do let me know the equivalent VBA code that would work. Thanks.
– S. Shaw
Nov 19 at 13:54
I just found out that I need to use Array function as the number of characters was exceeding 255. Even in Array function I needed to break the formula into two parts:
– S. Shaw
Nov 19 at 19:52
I just found out that I need to use Array function as the number of characters was exceeding 255. Even in Array function I needed to break the formula into two parts:
– S. Shaw
Nov 19 at 19:52
Dim FormulaPart1 As String Dim FormulaPart2 As String 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 Range("B1") .FormulaArray = FormulaPart1 .Replace "xxx()", FormulaPart2 End With strBulkBP = Range("B1")
– S. Shaw
Nov 19 at 19:55
Dim FormulaPart1 As String Dim FormulaPart2 As String 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 Range("B1") .FormulaArray = FormulaPart1 .Replace "xxx()", FormulaPart2 End With strBulkBP = Range("B1")
– S. Shaw
Nov 19 at 19:55
add a comment |
1 Answer
1
active
oldest
votes
up vote
0
down vote
accepted
'The below code works - using helper cells & then populating the variable. However the array formula needs to be split into two parts to make it less than 255 characters each. A better solution can be one where the variable directly stores the calculation.
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")
answered Nov 19 at 20:20
S. Shaw
65
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
0
down vote
accepted
'The below code works - using helper cells & then populating the variable. However the array formula needs to be split into two parts to make it less than 255 characters each. A better solution can be one where the variable directly stores the calculation.
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")
answered Nov 19 at 20:20
S. Shaw
65
add a comment |
up vote
0
down vote
accepted
'The below code works - using helper cells & then populating the variable. However the array formula needs to be split into two parts to make it less than 255 characters each. A better solution can be one where the variable directly stores the calculation.
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")
answered Nov 19 at 20:20
S. Shaw
65
add a comment |
up vote
0
down vote
accepted
up vote
0
down vote
accepted
'The below code works - using helper cells & then populating the variable. However the array formula needs to be split into two parts to make it less than 255 characters each. A better solution can be one where the variable directly stores the calculation.
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")
answered Nov 19 at 20:20
S. Shaw
65
'The below code works - using helper cells & then populating the variable. However the array formula needs to be split into two parts to make it less than 255 characters each. A better solution can be one where the variable directly stores the calculation.
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")
answered Nov 19 at 20:20
S. Shaw
65
answered Nov 19 at 20:20
S. Shaw
65
answered Nov 19 at 20:20
S. Shaw
65
answered Nov 19 at 20:20
S. Shaw
65
65
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53375968%2fpassing-output-of-one-evaluate-statement-to-another-getting-error-2015%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Is there a reason you're using
Evaluatefor this instead of the equivalent VBA?– Comintern
Nov 19 at 13:50
I need to store the value in a variable and use it after. Hence trying to use Evaluate method.
– S. Shaw
Nov 19 at 13:52
Do let me know the equivalent VBA code that would work. Thanks.
– S. Shaw
Nov 19 at 13:54
I just found out that I need to use Array function as the number of characters was exceeding 255. Even in Array function I needed to break the formula into two parts:
– S. Shaw
Nov 19 at 19:52
Dim FormulaPart1 As String Dim FormulaPart2 As String 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 Range("B1") .FormulaArray = FormulaPart1 .Replace "xxx()", FormulaPart2 End With strBulkBP = Range("B1")
– S. Shaw
Nov 19 at 19:55