Excel - Inserting formula with VBA

Question

Hello Stackoverflow friends,

I am struggling for 1 hour with a formula I would like to insert via VBA:

Formula = "=IFERROR(VLOOKUP(Q" & j & ";Table1[#All];2;FALSE);"""")"
ThisWorkbook.Worksheets("Sheet1").Cells(j, "AE").FormulaArray = Formula

I get the following error message:

Run-time error '1004' - Application-defined or object-definied error

Is there an issue with the brackets or double quotes?

Thanks!

Answer 1

Replace the semicolons with commas:

Formula = "=IFERROR(VLOOKUP(Q" & j & ",Table1[#All],2,FALSE),"""")"

OpenOffice uses semicolons to separate function parameters, Excel normally uses commas, and always uses commas when setting formulas in the above fashion.

Answer 2

When programming in any lanugage also in VBA - better not tied up user to specific regional settings or specific excel version. So instead of this:

Formula = "=IFERROR(VLOOKUP(Q" & j & ";Table1[#All];2;FALSE);"""")"
ThisWorkbook.Worksheets("Sheet1").Cells(j, "AE").FormulaArray = Formula

Better use this approach, when you determine exact user environment:

s = Application.International(xlListSeparator)
Formula = "=IFERROR(VLOOKUP(Q" & j & s +"Table1[#All]" + s + "2" + s + "FALSE)" + s + """"")"
ThisWorkbook.Worksheets("Sheet1").Cells(j, "AE").FormulaArray = Formula

p.s. I didn't checked the formula for the brackets etc. but just indicating the correct usage of list separator, and how to insert formulas with VBA code within cells in correct way.

As well, as previous post says - excel probably change the formula automatically when you open it. However excel do not change VBA code automatically, so be aware and pay attention to proper code in VBA.