Fix an Excel VBA Table Formula That Returns FALSE
Question details
The user needs to write a table formula to the correct cell using VBA without getting an unexpected FALSE result.

- 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.
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.
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.
In Excel, open Developer > Visual Basic, then find the procedure that adds the table 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.
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.
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.

Check Localized Formulas and Cell Assignments
Use these checks if the macro uses localized function names or the destination cell still shows an unexpected value.
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.

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.




