How to dynamically change parts of a complex xls function in VBA

Question

I'm pretty fresh to VBA and hope one of you can help.

I have a pretty complex xls function and need to be able to update parts of the function, that is the size of the table that it refers to.

Background is that I have a constantly growing table in say sheet2 in which I use array formulas to compile some of the information I need. Since these arrays consume a lot of processing power, I made a macro in which the table is converted back to a range, then a bunch of calculations are done and it's re-formated as a table. What I didn't consider is that this re-arranging of the table stops feeding my overview sheet1 with information from that table. So what I basically need is amending an array formula in my overview sheet so that it re-adjusts to the size of the table.

The array formula that i need to change is the following:

INDEX(Transactions!$O$20:$O$77,MAX(IF(Transactions!$D$20:$D$77=Overview!B8,IF(Transactions!$C$20:$C$77<=Overview!$B$5,IF(Transactions!$K$20:$K$77=Overview!F8,ROW(Transactions!$O$20:$O$77)-MIN(ROW(Transactions!$O$20:$O$77))+1))),1))

,where row 20 is fixed, but the table expands further down (e.g. the 77 will evenutally grow to 5,000 or so)

I'm pretty stuck on this and haven't generated much (useful) code yet.

    Sub UpdateOverview()

    Sheet2.ListObjects("Transactions").Unlist
    Dim last_row_T As Long
    last_row_T = Cells(Rows.Count, "C").End(xlUp).Row
    Sheet1.Range("H7").FormulaArray="=INDEX(Transactions!$O$20:$O$77,MAX(IF(Transactions!$D$20:$D$77=Overview!B8,IF(Transactions!$C$20:$C$77<=Overview!$B$5,IF(Transactions!$K$20:$K$77=Overview!F8,ROW(Transactions!$O$20:$O$77)-MIN(ROW(Transactions!$O$20:$O$77))+1))),1))"

    Sheet2.ListObjects.Add(xlSrcRange, Range("C19").CurrentRegion, , xlYes).Name = "Transactions"

    End Sub

I tried different versions of implementing the last_row_T into the formula, but that didn't work.

Ultimately, I need to change each of the 77 in the index formula accordingly to the growing size of the table - so it has to be a dynamic solution. Is there a way to do that or am I somewhere in nomansland?

riskypenguin · Accepted Answer · 2019-10-22T09:39:24.027

0

I suggest this simple fix plus a workaround for the 255 character limit of .FormulaArray:

Dim strFormula As String, newFormula As String
strFormula = "=INDEX(Transactions!$O$20:$O$lastrow,MAX(IF(Transactions!$D$20:$D$lastrow=Overview!B8," & _
            "IF(Transactions!$C$20:$C$lastrow<=Overview!$B$5,IF(Transactions!$K$20:$K$lastrow=Overview!F8," & _
            "ROW(Transactions!$O$20:$O$lastrow)-MIN(ROW(Transactions!$O$20:$O$lastrow))+1))),1))"
newFormula = Replace(strFormula, "lastrow", CStr(last_row_T))

Sheet1.Range("H7").FormulaArray = "FunctionPlaceholder()"
Sheet1.Range("H7").Replace "FunctionPlaceholder()", newFormula

See this question and this link for more information on the workaround.

edited Oct 22 '19 at 09:39

answered Oct 22 '19 at 09:08

riskypenguin

2,139
1
10
22

Hi, thanks for your quick response and I understand the syntax in general. Unfortunately, I get an error with this: 'Unable to set the FormulaArray property of the Range class' – Matze Oct 22 '19 at 09:24
`.FormulaArray` only takes up to 255 characters, you will need a workaround for that, give me a second for that. – riskypenguin Oct 22 '19 at 09:36
Thank you, it almost works perfectly now. The only thing is that it doesn't return an array formula afterall now – Matze Oct 22 '19 at 10:18

How to dynamically change parts of a complex xls function in VBA

1 Answers1