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-11 07:43:44Z

0

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

edited May 11, 2023 at 7:43

answered May 10, 2023 at 7:37

GWD

4,1481 gold badge27 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Buracku Over a year ago

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. 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 :)

Collectives™ on Stack Overflow

VBA Refreshing Table After Adding Formula to Cell

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related