Having Trouble with Formula Array VBA

[Pineapple_Crazy]

Hey Guys,

I seem to be having some difficulties getting a formula array to work in VBA. I'm not quite sure what I'm doing wrong, but could someone lend a hand? The way I currently have it set up it does nothing.

Thanks very much!

Sub Template()

'Turn off warnings and popups


Dim StrFile As String
Dim StrPath As String
Dim StrSheetName As String
Dim LastRow As Integer
Dim i As Integer
Dim theFormula1 As String
Dim theFormula2 As String
Dim theFormula3 As String
LastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row


Range("J12").Value = InputBox("Please enter invoice number.")


StrPath = "Y:\Finance\BI\Pete\JessicaPEARSONTemplate\"
StrFile = Dir(StrPath & "*Invoice*" & "*.xls*")


Do Until StrFile = ""


Workbooks.Open Filename:=StrPath & StrFile
StrSheetName = Sheets(1).Name
    
    With ThisWorkbook.Worksheets("Template")
    
theFormula1 = "=INDEX(F_F_F1(),F_F_F2())"
theFormula2 = "'[" & StrFile & "]" & StrSheetName & "'!R2C3:R2000C3, SMALL(IF(R12C10='[" & StrFile & "]" & StrSheetName & "'!R2C1:R2000C1,"
theFormula3 = "ROW('[" & StrFile & "]" & StrSheetName & "'!R2C1:R2000C1)-ROW('[" & StrFile & "]" & StrSheetName & "'!R2C1)+1), ROW(R[-22]C))"


    With ActiveSheet.Range("A23")
        .FormulaArray = theFormula1
        .Replace "F_F_F1())", theFormula2
        .Replace "F_F_F2()", theFormula3
    
    End With
    End With
    
Workbooks(StrFile).Close savechanges:=False


Range("A23").Select
Selection.AutoFill Destination:=Range("A23:A190"), Type:=xlFillDefault


StrFile = Dir()


Loop
End Sub

 

[Pineapple_Crazy]

FYI, your code from Post #5

Try changing this
.FormulaR1C1 = theFormula1
to this
.FormulaArray = theFormula1

And you would need to add an Iferror(yourformula,"") to avoid the #Num ! errors at the bottom of the resulting list.

Jon, I see what you are saying now. I apologize, was confused. This is a great solution and thanks.

 

[Pineapple_Crazy]

Ok, got the array problem figured out. It's working great thanks Stephen. I'm an idiot. When I exported my file from SSIS it was turning invoice number into text and not a number. The code below works great! Thanks guys!!

Sub Template()



Dim StrFile As String
Dim StrPath As String
Dim StrSheetName As String
Dim LastRow As Integer
Dim i As Integer
Dim theFormula1 As String
Dim theFormula2 As String
Dim theFormula3 As String


LastRow = Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row




Columns.EntireColumn.Hidden = False
Rows.EntireRow.Hidden = False
    


Range("J12").Value = InputBox("Please enter invoice number below.")




StrPath = "Y:\Finance\BI\Pete\JessicaPEARSONTemplate\"
StrFile = Dir(StrPath & "*Invoice*" & "*.xls*")


Do Until StrFile = ""


Workbooks.Open Filename:=StrPath & StrFile
StrSheetName = Sheets(1).Name




theFormula1 = "=INDEX(F_F_F1(),F_F_F2())"
theFormula2 = "'[" & StrFile & "]" & StrSheetName & "'!R2C3:R2000C3"
theFormula3 = "SMALL(IF(R12C10='[" & StrFile & "]" & StrSheetName & "'!R2C1:R2000C1,ROW('[" & StrFile & "]" & StrSheetName & "'!R2C1:R2000C1)-ROW('[" & StrFile & "]" & StrSheetName & "'!R2C1)+1), ROW(R[-22]C))"
Application.ReferenceStyle = xlR1C1


With ThisWorkbook.Worksheets("Template").Range("A23")


    .FormulaArray = theFormula1
    .Replace what:="F_F_F1()", replacement:=theFormula2, lookat:=xlPart
    .Replace what:="F_F_F2()", replacement:=theFormula3, lookat:=xlPart
   
        
End With
Application.ReferenceStyle = xlA1


Workbooks(StrFile).Close savechanges:=False


Range("A23").Select
Selection.AutoFill Destination:=Range("A23:A190"), Type:=xlFillDefault




StrFile = Dir()


Loop




End Sub