Geek Answers Handbook

Excel - Insert Formula with VBA

Friends of Hello Stackoverflow,

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

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

The following error message appears:

Runtime Error '1004' - Defined or object-defined error

Is there a problem with brackets or double quotes?

Thanks!

+2
vba excel-vba excel excel-formula
source share
3 answers

Replace the semicolons with commas:

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

OpenOffice uses a comma to separate function parameters, Excel typically uses commas, and always uses commas when setting up formulas in the way above.

+5
source share

Depending on regional settings, the separator list (which is also used to separate parameters in functions) is either a semicolon or a semicolon. This applies when entering a formula in a cell.

Excel dynamically adjusts the delimiter list (and function names) according to the regional settings of the current computer when opening the file.

So, if a user with a German regional setting that has a list separator ; saves the file, then the US user regional settings and the list separator , opens the same file, Excel will adjust the German list of separators in the formulas automatically.

When writing VBA, you always need to use US-English conventions for the list separator, which is a comma.

+2
source share

When programming in any language also in VBA - it is better not to bind the user to specific regional settings or a specific version of Excel. So instead:

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

It is better to use this approach when you define the exact environment of the user:

 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

ps I did not check the formula for parentheses, etc., but simply indicated the correct use of the list separator and how to correctly insert the formulas with VBA code into the cells.

As in the previous post, Excel is likely to automatically change the formula when it opens. However, Excel does not automatically change the VBA code, so you should remember and pay attention to the correct code in VBA.

0
source share

More articles:


All Articles