Trouble with FormulaArray limitations and Method 'Range' of object '_Global' Failed

[chikiny]

Hello,

Im trying to make something that will manipulate data consistently across multiple workbooks. I am having trouble with the 255 character limit of the FormulaArray function (I tried the .replace workaround and I'm not sure if I did it correctly) and when I'm not getting that error I'm getting that error im getting RunTime Error 1004 Method 'Range' of Object '_Global' Failed. Here is what I have

--------------------------------------------------------------------
Sub autoFill()
'
' Macro2 Macro
'
'
Dim rowSelect As Integer
Dim formulaPart1 As String
Dim formulaPart2 As String
Dim totalFormula As String
Dim YODA As String
Dim columnSelect As Integer
Dim firstCell As Range

formulaPart2 = "Book1.xlsx!Table1[#Data]"
formulaPart1 = "=IFERROR(INDEX(" + YODA + ", SMALL(IF(ISNUMBER(FIND(A4, " + YODA + ")), ROW(" + YODA + ")-MIN(ROW(" + YODA + "))+1, ""), ROW(" + YODA + ")-MIN(ROW(" + YODA + "))+1), COLUMN(" + YODA + ")-MIN(COLUMN(" + YODA + "))+1), "")"
Cells.Find(What:="file_name", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Select
rowSelect = Selection.Row
columnSelect = Selection.Column
Set firstCell = Range(Cells(rowSelect + 1, 1), Cells(rowSelect + 1, 1))
MsgBox (firstCell.Row)
MsgBox (firstCell.Column)
Cells(firstCell.Row, firstCell.Column).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Cells(firstCell.Row, firstCell.Column).EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
Cells(rowSelect + 1, 1).Formula = "=TRIM(R[-1]C)"
Set firstCell = Range(Cells(rowSelect + 2, 1), Cells(rowSelect + 2, 1))
Range(firstCell).FormulaArray = formulaPart1
Range(firstCell).Replace "YODA", formulaPart2
Range(Cells(rowSelect + 1, 1), Cells(rowSelect + 2, 1)).Select
Selection.autoFill Destination:=Range(Cells(rowSelect + 1, 1), Cells(rowSelect + 2, columnSelect)), Type:=xlFillDefault
Range(Cells(rowSelect + 1, 1), Cells(rowSelect + 2, 1)).Copy
Range(Cells(rowSelect + 1, 1), Cells(rowSelect + 2, columnSelect)).PasteSpecial Paste:=xlValues
End Sub

----------------------------------------------------------------

My code breaks at the redcolored line. Can anybody give me any help? Thanks.

 

[Ferdi24]

To test formulas being set through VBA I always test them first:
1. CTRL + G
2. Type ? and then paste your formula.

If do this with yours I get:

=IFERROR(INDEX(, SMALL(IF(ISNUMBER(FIND(A4, )), ROW()-MIN(ROW())+1, "), ROW()-MIN(ROW())+1), COLUMN()-MIN(COLUMN())+1), ")

This is not a working formula. Your string varaible Yoda is never set or filled so results in nothing, hence nothing is added to the formula, which makes it break.

 

[chikiny]

The problem is that when I replace YODA with the string 'formulaPart2' I go over the 255 character limit of the FormulaArray property. That is supposed to be the function of the .Replace function. Is it supposed to be used as a VBA workaround for the character limit placed onto the FormulaArray property. I guess my next question is how do I find a way to place a 350 character function into a 255 character limit property. Everything thatI have looked at states that the .Replace function is the best method to do so but I am just unsure of how to use the function.