VBA & Macro Problems

Fix an Excel VBA Table Formula That Returns FALSE

Algirdas JasaitisAlgirdas Jasaitis Nov 18, 2026 869 views

Question details

The user needs to write a table formula to the correct cell using VBA without getting an unexpected FALSE result.

Fix an Excel VBA Table Formula That Returns FALSE
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro adds a row to an Excel table and writes a lookup formula that uses structured references.
Observed behavior
The formula returns FALSE or does not produce the expected result after the macro writes it.
Before you start

Check the table name, the destination column, and the names of the CATERING and PAX columns. Make sure the Datos sheet contains the lookup range A2:B18 and that you can run the macro in desktop Excel.

Solution 1Recommended

Write the English Formula to the New Table Row

Use this approach when the VBA formula uses English function names and should be placed in the eighth column of a newly added table row.

1
Open the VBA editor

In Excel, open Developer > Visual Basic, then find the procedure that adds the table row.

2
Add and capture the row

Use a ListRow variable to capture the new row, for example: Set newRow = Worksheets("Sheet1").ListObjects("Table1").ListRows.Add. Replace Sheet1 and Table1 with your actual worksheet and table names.

3
Assign the formula

After adding the row, use: newRow.Range.Cells(1, 8).Formula = "=IFERROR(VLOOKUP([@CATERING],Datos!A2:B18,2,FALSE)*[@PAX],"""")". The doubled quotation marks represent an empty string inside the VBA formula.

4
Check the result

Run the macro and select the eighth cell of the new row. Check the formula bar to confirm that it contains the formula, then verify the lookup and PAX values.

Write the English Formula to the New Table Row
What FALSE means in the formula: FALSE is the VLOOKUP argument for an exact match; it does not by itself make the formula return FALSE. IFERROR returns a blank string when the lookup or calculation produces an error.
Free Microsoft Office alternative

Work with Excel Tables in WPS Spreadsheet

WPS Office is a free, lightweight alternative for opening and editing spreadsheets in a familiar interface. Its Excel format compatibility makes it easier to move existing workbooks to WPS, although it does not fix Excel-specific VBA issues.

Open and edit Microsoft Excel XLSX and XLS filesWork with table data and spreadsheet formulasUse a familiar spreadsheet interface for an easier transitionAccess a free, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

Should I use Formula or FormulaLocal in VBA?

Use the Range.Formula property for formulas written with English function names and commas. Use Range.FormulaLocal when the formula uses the function names and separators of the local Excel installation.

Why does the formula contain FALSE?

In VLOOKUP, FALSE requests an exact match. It is an argument, not necessarily the displayed result. Check the formula bar and the macro's later assignments if the cell displays FALSE.

How do I make sure the formula goes into the new row?

Capture the result of ListRows.Add in a ListRow variable, then assign the formula through that variable's Range, such as newRow.Range.Cells(1, 8).

Why is the result blank instead of a lookup value?

The example uses IFERROR to return an empty string when the lookup or calculation fails. Check the CATERING value, the Datos!A2:B18 lookup range, and the PAX value.

Can assigning .Value remove the formula?

Yes. A later .Value assignment to the same cell replaces its formula. Review the VBA procedure after the .Formula or .FormulaLocal assignment.