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

 

[StephenCrump]

Try this:

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 ActiveSheet.Range("A23")
    .FormulaR1C1 = theFormula1
    .Replace what:="F_F_F1()", replacement:=theFormula2, lookat:=xlPart
    .Replace what:="F_F_F2()", replacement:=theFormula3, lookat:=xlPart
End With
Application.ReferenceStyle = xlA1

But I think your code is a bit muddled about where you want to put these formulae?

My guess is that you want these in ThisWorkbook.Worksheets("Template"), whereas at present you write them to ActiveSheet, which will be in the *Invoice* workbook you open and close without saving.

Also, isn't your code going to overwrite A23:A190 on each iteration of the loop?

 

[Pineapple_Crazy]

Try this:

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 ActiveSheet.Range("A23")
    .FormulaR1C1 = theFormula1
    .Replace what:="F_F_F1()", replacement:=theFormula2, lookat:=xlPart
    .Replace what:="F_F_F2()", replacement:=theFormula3, lookat:=xlPart
End With
Application.ReferenceStyle = xlA1

But I think your code is a bit muddled about where you want to put these formulae?

My guess is that you want these in ThisWorkbook.Worksheets("Template"), whereas at present you write them to ActiveSheet, which will be in the *Invoice* workbook you open and close without saving.

Also, isn't your code going to overwrite A23:A190 on each iteration of the loop?

Hey Stephen thanks so much for the reply. Yes I indeed want it to open the "invoice" worksheet first and populate the "template" sheet which is in a different workbook. How do I make this happen? Also I want it to overwrite column A, as when the person runs the code they will enter in a unique invoice number to populate the template worksheet. Thanks again for all the help!!

 

[Jonmo1]

Sometimes when trying to get a complicated/long array formula into cells with VBA, it's good to step back and ask
"Do I really need this to be done with formula?"

Since you're already using VBA, maybe there's an alternate method to accomplish the task without the complicated formula.

I gather the formula is extracting a list of values that meet a criteria.
Sounds like something you can do with AutoFilter.

Try by hand at first just to see if it's doable.
Turn on the autofilter for the source data sheet
Filter Column A for the desired criteria, and copy the visible cells to your destination sheet.

If that is workable, you can record yourself doing this by hand using the Macro Recorder.
The resulting code can be tweeked to automate what crieria is used in the filter.

 

[Pineapple_Crazy]

Stephen, I was able to get it to work using the code below. However, it only populates cell A23 and when it auto fills down to A190 I receive #NUM !. The formula does not appear to be an array either, but works fine in A23. I do need this formula to fill down to A190 though. Thanks again!

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
       
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")
    .FormulaR1C1 = 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

 

[Pineapple_Crazy]

Sometimes when trying to get a complicated/long array formula into cells with VBA, it's good to step back and ask
"Do I really need this to be done with formula?"

Since you're already using VBA, maybe there's an alternate method to accomplish the task without the complicated formula.

I gather the formula is extracting a list of values that meet a criteria.
Sounds like something you can do with AutoFilter.

Try by hand at first just to see if it's doable.
Turn on the autofilter for the source data sheet
Filter Column A for the desired criteria, and copy the visible cells to your destination sheet.

If that is workable, you can record yourself doing this by hand using the Macro Recorder.
The resulting code can be tweeked to automate what crieria is used in the filter.

Hey Jonmo, thank you! I've actually become pretty decent with VBA through the years, although I'm more of a SQL guy. The one thing I always struggle with is the freaking arrays because of that 255 max character. I can never really seem to get them to work right. In this case though I really need to use an array formula, as no other formula seems capable to do what I want. Stephen has provided a great answer though, I'm just having issues populating the entire sheet.

 

[Jonmo1]

as no other formula seems capable to do what I want.

My point is, Do you really 'need' it to be a formula at all?

 

[Pineapple_Crazy]

My point is, Do you really 'need' it to be a formula at all?

I don't see how that would work? I have someone enter an invoice number, which is then used to look up values from another worksheet to populate my template based on that particular invoice number. The invoice number has rows and rows of data associated with it. The user wants to be able to enter in an invoice number, I'm not sure how auto-filters apply unless I'm missing something.

 

[Jonmo1]

Here is a small 'from scratch' example..

Copies from Sheets(1) to Sheets("Template")
Assumes Row 1 in Sheets(1) is headers, begins the copy from Row2.
Uses an input box for the invoice number and autofilters sheets(1) column A on that criteria
Copies the visible data from column C to Sheets("Template") range A23

Sub InvoiceFilter()
Dim InvNum
InvNum = InputBox("Please enter invoice number.")
With Sheets(1).Range("A1:C2000")
    .AutoFilter field:=1, Criteria1:=InvNum
    .Offset(1, 2).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible).Copy Sheets("Template").Range("A23")
    .AutoFilter
End With
End Sub

You'd have to some tweaking to get the book and sheet names correct.

 

[Jonmo1]

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.