VBA FormulaArray Runtime Error

[johanssson]

Hi all,

Have been stucked in a formula for hours now, cant really figure out what i'm doing wrong in this formula, maybe several mistakes by me or maybe something little hat i just missed:

Range("T" & rng.Row).Select
Selection.FormulaArray = "=IFERROR(INDEX(Tabell_Tidsrapport.accdb15[Effektiv tid E];MATCH(TidRapp_Expo!B28&TidRapp_Expo!K28;Tabell_Tidsrapport.accdb15[Date2]&Tabell_Tidsrapport.accdb15[Anställnings Nr:];0));"""")"


What it basically should do is to select the column T in my range.row and insert an arrayformula
Error message is :

Run Time Error '1004'
Unable to Set the FormulaArray Property of the Range Class

the formula itself works in the worksheet but i need to add it via a macro.

 

[Norie]

Does it work if you replace the ; with , in the formula?

 

[johanssson]

No, I'm running a Swedish version of excel so i have just changed the ISERROR & MATCH for easier understanding.
Swedish excel versions use ; instead of ,
INDEX is the same in SWE and ENG version.

 

[Norie]

I know the Swedish version of Excel uses ; instead of , but VBA can sometimes be a bit US-centric.

Have you tried using , instead of ; and the US versions of the functions?

 

[johanssson]

Can i in someway give you an kiss!

It worked directly!!! Thanks!!!

 

[johanssson]

Maybe one more issue.
The code put the formula correctly into correct cell.
Problem is that i need to click on each cell afterwatd Ctrl-Shift + ENTER even if there is an array formula that has been copied to correct cell.

in cells it just says #NAME?

Blad14.Range("T30").Select
Selection.FormulaArray = "=OMFEL(INDEX(Tabell_Tidsrapport.accdb15[Effektiv tid E],PASSA(TidRapp_Expo!B30&TidRapp_Expo!K30,Tabell_Tidsrapport.accdb15[Date2]&Tabell_Tidsrapport.accdb15[Anställnings Nr:],0)),"""")"

 

[Norie]

Did you try replacing OMFEL with IFERROR, PASSA with MATCH etc?

 

[johanssson]

I just did what you wrote, and yes! now I've got the values many thanks Noire

 

[Norie]

No problem.

I think you can use your own local settings/formula names for 'standard formulas' by using FormulaLocal and FormulaR1C1Local but I can't find an equivalent for array formulas.