3

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!

Community
  • 1
  • 1

3 Answers3

5

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.

Bathsheba
  • 231,907
  • 34
  • 361
  • 483
  • 2
    WRONG, depends on your region settings. I'm from Belgium and use `;` to seperate parameters. – CustomX May 16 '13 at 09:54
  • Is still the first thing I'd check though; the formula seems fine to me otherwise; the quotes are all correct, Table1[#All] is the other potential culprit but we'd need to see the workbook to check that. – Bathsheba May 16 '13 at 10:02
  • Hmmm true, but it's wrong to say Excel only uses commas ;) If you edit your answer, I can remove my downvote, I see TS accepted it as an answer ;) – CustomX May 16 '13 at 10:04
  • 1
    I took the downvote like a cat my friend ;-) Actually I had no idea that you can change the function delimiter in Excel. To be rigourous then I suggest you need a way of accessing the delimiter in use so the Formula assignment above is portable between Excel users. – Bathsheba May 16 '13 at 10:20
  • 5
    @t.thielemans in VBA `.Formula`, `.FormulaArray` and `.FormulaR1C1` use international seperator (ie `,`) and `.FormulaLocal`, and `.FormulaR1C1Local` use the language of the user (so can use `;` if that is your language setting). So for this OP assigning to `.FormulaArray` it is correct to say always use `,` – chris neilsen May 16 '13 at 10:53
  • @chrisneilsen, thank you for your comment, didn't know about that. I feel like an idiot now :$ Ithink I seriously mixed those up – CustomX May 16 '13 at 11:57
4

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.

Silvestr
  • 41
  • 2
2

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

Excel dynamically adjusts the list separator (and function names) according to the regional settings of the current computer when a file is opened.

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

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

teylyn
  • 34,374
  • 4
  • 53
  • 73