VBA Refreshing Table After Adding Formula to Cell

Question

I wrote some VBA code that adds a column to a table with a specific name ('test' in this case) and creates a sheet with that specific name (also 'test').

The 'test' table column then has its cells populate from the following VBA line.

Sheets("Data (HIDE)").ListObjects("DataTable").ListColumns(Range("B2").Value).DataBodyRange.Cells(1).Formula = "=IFERROR(VLOOKUP(A6,'" & Range("B2").Value & "'!$A$2:$B$1500,2,FALSE),"""")"

Here's what the result looks like:

The issue happens when I go to the 'test' sheet and populate it with values to be looked up on this table. I populate the 'test' sheet with data and go back to this table only to find that the VLOOKUP formula doesn't calculate for all rows until I double-click on a cell containing the VLOOKUP formula and press ENTER.

Is there a way in VBA to make it so that I don't have to manually click on the table to cause the VLOOKUP formulas to refresh/recalculate?

GWD · Accepted Answer · 2023-05-11T07:43:44.027

You can try Range.Calculate, and sometimes if that is not enough, combine it with Range.Dirty

In your case

With ThisWorkbook.Worksheets("Data (HIDE)")
    With .ListObjects("DataTable").ListColumns(.Range("B2").Value).DataBodyRange
        'Sometimes depending on whether formulas are volatile and their interdependencies:
        .Cells.Dirty

        'To recalculate all the cells of the ListObject:
        .Cells.Calculate

        'To recalculate only one:
        .Cells(1).Calculate
    End With
End With

Edit:

It seems like this doesn't always work, I'll quote the solution from OPs comment for easier future reference:

Solution by @Buracku as per comment to this post:

With ThisWorkbook.Worksheets("Data (HIDE)")
    With .ListObjects("DataTable").ListColumns(.Range("B2").Value).DataBodyRange
        .Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, _
                       SearchOrder:=xlByColumns, MatchCase:=False, _
                       SearchFormat:=False, ReplaceFormat:= False, _
                       FormulaVersion:=xlReplaceFormula2
    End With
End With

Appreciate the help, although that didn't work. After hours of troubleshooting to no avail, it sounds like an issue with Excel and populating cells with formulas through VBA. https://stackoverflow.com/a/39153031/10382803 Used the following VBA to get it to FINALLY work. Cells.Replace What:="=", Replacement:="=", LookAt:=xlPart, SearchOrder _ :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:= _ False, FormulaVersion:=xlReplaceFormula2 May be kind of hacky, but for my use case it works :) — Buracku, May 11 '23 at 00:56

VBA Refreshing Table After Adding Formula to Cell

1 Answers1