INDEX and AGGREGATE Function in Excel VBA

Question

I'm trying to move an in cell formula to VBA, because otherwise it's always recalculating, even when I deactivate the excel option, it comes back when I reopen the file. That's why I want to move that formula to VBA, where it happens only when I press a button, which is much smarter.

I have a master table with data, which I aggregate and index and express it on another sheet in a table. -> column A to S are in the master table, in the aggregated table, I will only have column A,C,E,G,H,I,J,K,L,M and P

The formula I want to move to VBA is the following:

=IFERROR(INDEX(Endkontrolle!$A:$S;AGGREGATE(15;6;ROW(Endkontrolle!$A:$S)/((FIND($B$3;Endkontrolle!$F:$F;1)>0)*(Endkontrolle!$S:$S="x"));ROW()-32)-0;1);"")

Can somebody help me translate that formula to VBA script?

thank you very much

Answer 1

Try this code:

Sub Button1_Click2()
    
    'Declarations.
    Dim RngTable As Range
    Dim RngTarget As Range
    Dim StrColumnsIndex As String
    
    'A string is used to stores the index of the columns to be copied.
    StrColumnsIndex = "1;3;5;7;8;9;10;11;14;15;16"
    
    'RngTable is set as the range that will host the aggregated table.
    Set RngTable = Sheets("Aggregated sheet").Range("A33:K34") '< EDIT THIS LINE ACCORDGLY TO YOU NEED
    
    'Clearing RngTable.
    RngTable.ClearContents
    
    'Checking if StrColumnsIndex and RngTable are compatible.
    If UBound(Split(StrColumnsIndex, ";")) + 1 <> RngTable.Columns.Count Then
        MsgBox "The number of columns requested via StrColumnsIndex and the number of columns avaiable in RngTable do not match. Redefine the variables properly. The aggregated table will not be updated.", vbCritical + vbOKOnly, "Variable mismatch"
        Exit Sub
    End If
    
    'Covering each cell in RngTable.
    For Each RngTarget In RngTable
        
        'The result is reported in each cell. The [row] element of the INDEX is obtained by subtracting _
        RngTable.Row from the RngTarget.Row and adding one. This way each row is properly reported. The _
        [col] element of the INDEX is obrained by splitting StrColumnsIndex using the difference between _
        the RngTarget.Column and RngTable.Column as index. This way each requested column as listed in _
        StrColumnsIndex is reported.
        'RngTarget.Formula = "=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x""))," & RngTarget.Row - RngTable.Row + 1 & ")-0," & Split(StrColumnsIndex, ";")(RngTarget.Column - RngTable.Column) * 1 & "),"""")"
        RngTarget.Value = Evaluate("=IFERROR(INDEX(Endkontrolle!A:S,AGGREGATE(15,6,ROW(Endkontrolle!A:S)/((FIND(B3,Endkontrolle!F:F,1)>0)*(Endkontrolle!S:S=""x""))," & RngTarget.Row - RngTable.Row + 1 & ")-0," & Split(StrColumnsIndex, ";")(RngTarget.Column - RngTable.Column) * 1 & "),"""")")
        
        'If RngTarget contains nothing then it's assumed there are no more results to be reported and the macro is terminated.
        If RngTarget.Value = "" Then Exit Sub
        
    Next
    
End Sub