NicholasP
Active Member
- Joined
- Nov 18, 2006
- Messages
- 291
So I've been working on a longer array formula and I feel like I've got it down pretty well as far as what other people on MrExcel have suggested, but I'm falling a little short. The code runs without error now, but the .Replace portion of the code isn't working. Does anyone have any thoughts?? Any help would be greatly appreciated
I'm trying to execute the suggestion offered here (that has been suggested several times to this question on MrExcel):
Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA
Thanks
Nick
I'm trying to execute the suggestion offered here (that has been suggested several times to this question on MrExcel):
Daily Dose of Excel » Blog Archive » Entering Long Array Formulas in VBA
Thanks
Nick
Code:
Sub test()
Range("H2").Select
Dim theFormulaPart1 As String
Dim theFormulaPart2 As String
Dim AA As String
AA = "'[2015 July 20_Daily Fantastical Supplement.xlsm]XYZ Fantastical Supplement'"
theFormulaPart1 = "=INDEX(" & AA & "!R145C3:R10000C3," & "X_X_X)"
theFormulaPart2 = "MATCH(RC[-5]&""AY70""," & AA & "!R145C11:R10000C11&" & AA & "!R145C10:R10000C10,0))"
With Selection
.FormulaArray = theFormulaPart1
.Replace "X_X_X)", theFormulaPart2 ''''<<---this portion is not working
End With
End Sub