Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

formula not correct with vba

0

Hello,
Why does my formula with vba code
Messed up, this code from vba will show on the master sheet on B9 the formula
but Instead of

=SORT

It shows

=@SORT
        ' Initialize formula string
        formulaString = ""
        firstFilter = True

        ' Include filters for existing tables in all worksheets
        For Each ws In ThisWorkbook.Worksheets
            For Each tbl In ws.ListObjects
                If tbl.Name Like "TBL_*" And tbl.Name <> newTableName Then
                    If Not firstFilter Then
                        formulaString = formulaString & ", "
                    End If
                    formulaString = formulaString & "FILTER(" & tbl.Name & ", " & tbl.Name & "[MASTER] = TEXTAFTER(CELL(""filename"", A1), ""]""), """")"
                    firstFilter = False
                End If
            Next tbl
        Next ws

        ' Add the new table to the formula
        If formulaString <> "" Then
            formulaString = "=SORT(VSTACK(" & formulaString & ", FILTER(" & newTableName & ", " & newTableName & "[MASTER] = TEXTAFTER(CELL(""filename"", A1), ""]""), """")), 6, 1, FALSE)"
        Else
            formulaString = "=SORT(FILTER(" & newTableName & ", " & newTableName & "[MASTER] = TEXTAFTER(CELL(""filename"", A1), ""]""), """")), 6, 1, FALSE)"
        End If

        ' Set the formula in cell B9 of the "Master" sheet
        On Error Resume Next
        masterSheet.Range("B9").ClearContents
        masterSheet.Range("B9").Formula = formulaString

Any idea how to solved that?
Thank you

Answer
Discuss

Answers

0
Selected Answer

Seems in 365 vba code can be messed up somehow But it seems i manged it by changing the .Formula to .Formula2 and surprisingly it worked for setting the dynamic array formula without the @ operator.
Thanks.

Discuss

Discussion

Hi again Ghost

That's because .Formula2 replaces .Formula in the versions of Excel which fully support dynamic arrays, functions and formulae i.e. in both Excel 365 and Excel 2021.

Earlier versions don't support them (and you need Ctrl+Shift+Enter to add an array formula) E.g. in Excel 2019, .Formula will work but .Formula2 won't.
John_Ru (rep: 6417) Aug 5, '24 at 6:52 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login